10 Replies Latest reply on Feb 11, 2019 8:59 PM by Kielty Walsh

# Date Difference in Minutes, Excluding weekends

I have seen this general issue discussed in a couple of forums, however my case scenario is a bit different - Looking to see if anyone has run into this.

I am looking to get the difference in minutes between two dates. I do not want to count minutes after hours or on the weekends. However some of my start dates begin outside of work hours, and on the weekends. For example we will receive an email at 7:55 am on a Saturday. And we respond at 10:30am on a Monday. I am looking to only have it count the minutes from opening at 8am till we responded at 1030am?

In addition I will want to exclude public holidays, however I would assume I can use similar logic from above, and other threads to solve for that.

Thanks!

• ###### 1. Re: Date Difference in Minutes, Excluding weekends

Hi Kielty, could you include some sample data, and the exact hours during which minutes should be counted?

Thanks,

Jennifer

• ###### 2. Re: Date Difference in Minutes, Excluding weekends

Hi Kielty,

Just use 'weekday' in your calculation for the date part. - i.e.

`DATEDIFF('weekday', #2013-09-22#, #2013-09-24#, 'monday')`

HTH

Peter

1 of 1 people found this helpful
• ###### 3. Re: Date Difference in Minutes, Excluding weekends

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).

• ###### 4. Re: Date Difference in Minutes, Excluding weekends

Hi Peter,

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.

Thanks!

• ###### 5. Re: Date Difference in Minutes, Excluding weekends

Hi Kielty,

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

Regards,
Anupam

• ###### 6. Re: Date Difference in Minutes, Excluding weekends

Please share your workbook so that it will be easy to work on it.

Thanks

Abdul

• ###### 7. Re: Date Difference in Minutes, Excluding weekends

Hi Kielty,

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.

HTH

Peter

• ###### 8. Re: Date Difference in Minutes, Excluding weekends

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.

• ###### 9. Re: Date Difference in Minutes, Excluding weekends

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?

Dan

1 of 1 people found this helpful
• ###### 10. Re: Date Difference in Minutes, Excluding weekends

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

AFL 27/0/2019

Melbourne Cup 5/11/2019

Christmas Day 25/12/2019

Boxing Day 26/12/2019