Hi Kielty, could you include some sample data, and the exact hours during which minutes should be counted?
1 of 1 people found this helpful
Just use 'weekday' in your calculation for the date part. - i.e.
DATEDIFF('weekday', #2013-09-22#, #2013-09-24#, 'monday')
Start Time End time
10/1/2019 10:44:1 AM 02/01/2019 10:18:13 AM
09/1/2019 9:44 AM 11/01/2019 2:47:33 PM
12/01/2019 4:06:55 PM 12/01/2019 5:02:07 PM
So I am trying to find the difference in minutes between these two time stamped dates, within working hours (not counting minutes outside of 8am-6pm M-F).
Unfortunately that formula just returns the amount of days in-between the dates. And I need the amount of minutes that occur between the two dates during working hours.
To give context I am looking at speed of return. But these returns can only be done during working hours (M-F 8am-6pm).
Let me know if hearing more context would help.
Hope below link will help you out to achieve result you are looking for. the solution is for day you will need to multiply with minutes i.e. 60*9 for 9 hours per day
Please share your workbook so that it will be easy to work on it.
Sorry, I figured you didn't need much assistance as you didn't upload a .twbx to work with. I presumed that you would nest the weekday statement inside another calculated field aimed at obtaining the minutes that you need. As Abdul noted above, Grateful if you could share a workbook because the calculation will be fairly complex to work out.
Ok, here is the basic dates I am working with. Again just to clarify, I am trying to find the minutes that occur between the two dates during working hours (8am-6pm, M-F) Emails received over the weekend, I would like to start counting minutes as of 8am Monday morning.
DateDif.twbx 108.9 KB
1 of 1 people found this helpful
Actually, it is the holidays that is really the hardest part of solving this problem. To handle the holidays, you will want a separate date table in your database that marks which days are holidays and which are not.
If you have a very short list of holidays, you can subtract them off one-by-one but that will be slow and complex. I assume emails can come in on a holiday. I don't know whether a response can ever happen on a holiday.
The article Anupam links to explains how to count the days with and without holidays.
You can then add the number of minutes from the first day and the last day.
The approach I described in Yet another ticket SLA/business day question - with a twist? could also be used with some modifications.
Can you share the list of holidays you want to check and make sure your sample data includes some periods that cross a holiday?
Was able to solve the minute and date function problem with a bit of modification to the same logic as above. Thanks Dan!
Starting on the public holidays now. The list isn't very long. No responses will ever be made on a holiday, we will just receive messages.
Here is the list of Holidays being used - the January data does include these holidays. Currently manually subtracting.
New Years Day 1/1/2019
Australia Day 28/1/2019
Labour Day 11/03/2019
Good Friday 19/04/2019
Easter Monday 22/04/2019
Anazac Day 25/04/2019
Queens Birthday 10/6/2019
Melbourne Cup 5/11/2019
Christmas Day 25/12/2019
Boxing Day 26/12/2019