Calculating days, hours, min, and sec between two dates excluding weekends

Hi there,

We have a ticketing system that timestamps when a ticket is created and when it is closed, and I am trying to calculate the sum of time in d:hh:mm:ss between the two but excluding weekends (US). I am aware of NETWORKDAYS() but that works with the exception of a few instances. Tickets open on a Wednesday and closed the next Wednesday are throwing me for a loop.

For example, in the last row of the table in the file we have calculated 6 net working days (wed to wed) but an elapsed time of 7.17 days when looking at the open and closed date. Shouldn't subtracting 2 weekend days (Sat + Sun) make it 5.17 days?

It can be confusing.

I find it best to remember that when subtracting dates the result is end date exclusive. With NETWORKDAYS the result is start and end date inclusive. That's why you'll often see +1 following date subtractions in order to include that closing date.

If my "explanation" doesn't help try subtracting TODAY's date from TODAY's date. The result is of course 0. Then use TODAY's date for both the start and end date in NETWORKDAYS. It returns 1 (assuming not a weekend).

Did that help?

Originally Posted by Crystalyzer

NETWORKDAYS returns the number of WHOLE working days between two dates where your elapsed time calculation is taking into account the start and end times. There are 8 days Wed. to Wed. and taking out 2 weekend days leaves 6 whole days.

To get what you are looking for you would have to adjust your Elapsed Time (d:h) formula to be

Formula:

Please Login or Register  to view this content.

I don't think this is working as intended, for example the Elapsed Time in the first row when converted to hh:mm:ss does not show less 48:00:00 with your updated formula. The original elapsed time is 60:41:53, minus 48:00:00 (Sat and Sunday hours = 48h), should be 12:41:53. Shouldn't the corrected formula show that too?

Appreciate the help!

What if you tried this:

Formula:

Please Login or Register  to view this content.

Edited }I failed to mention that this 're-purposes' NETWORKDAYS.INTL count the weekends in order to subtract them from the Modified-Created differences.

It returns this:

1.53
1.67
1.35
1.64
1.74
1.64
3.35
3.92
4.95
3.95
3.18
5.01
6.85
6.17

You are welcome. Thank you for the feedback, the added rep and marking your thread Solved.

Since there was no formula under 'Working ... ' I cannot tell. But it does appear you are mixing units. Your formula in 'Elapsed time' is calculating days, hours and minutes. The formatting is consistent with that. I don't know how you derived 'Working' but the formatting is General.

My "instincts" tell me you tried to subtract 2 days from 134.+++ hours ..... that would be where the 132 came from.

That's because NETWORKDAYS returns the number of days. The reason 'Elapsed time' returns days and times is due to subtracting the dates and times and the formatting.

And BTW I really encourage the use of NETWORKDAYS.INTL. It is far more versatile (as is WORKDAY.INTL). You then add the times back in.

That said try this formula in K5

Formula:

Please Login or Register  to view this content.

Custom format d.hh:mm:ss

Row 13 now returns 11.20:32:30

Is that what you were looking for?

Arrgghhh!

I forgot. There's a limitation on Custom formatting of days. Number of days returned top out at 31 days and then 'wrap around' starting over again at 1.

This came up a few years back and I do not recall if there is a work-around for it except to concatenate the days and times. This renders the results text and useless for calculations.

Like this:

Formula:

Please Login or Register  to view this content.

In the meantime I'll put out a call for community help.

Edit I just did that. May take a while.

Originally Posted by FlameRetired

Arrgghhh!

I forgot. There's a limitation on Custom formatting of days. Number of days returned top out at 31 days and then 'wrap around' starting over again at 1.

This came up a few years back and I do not recall if there is a work-around for it except to concatenate the days and times. This renders the results text and useless for calculations.

Like this:

Formula:

Please Login or Register  to view this content.

In the meantime I'll put out a call for community help.

Edit I just did that. May take a while.

Appreciate you going the extra mile!

I borrowed your idea and took it a little bit further and found it works for all days where the case was not created or closed (or both) on the weekend. For the cases that were closed/opened--or both--on the weekend I realize I'd have to build conditional statements around these:

Starts on a Saturday, ends on a week day
Starts on a Sunday, ends on a week day
Starts on a Saturday, ends on a Sunday
Starts on a Sunday, ends on a Saturday
Starts on a week day ends on a Saturday
Starts on a week day ends on a Sunday

Please Login or Register  to view this content.

I'm sure there's a simpler way to solution this as I tend to make these things more complicated than they need to be!

Starts on a Saturday, ends on a week day
Starts on a Sunday, ends on a week day
Starts on a Saturday, ends on a Sunday
Starts on a Sunday, ends on a Saturday
Starts on a week day ends on a Saturday
Starts on a week day ends on a Sunday

IF weekend is Sat and Sun, row 5 result should be 5 days 2 hours

=CONCAT(TRIM(LEFT(SUBSTITUTE(TEXT(NETWORKDAYS(D5,E5,Table296)-SUM(MOD(D5:E5*{1,-1},1)*NETWORKDAYS(+D5:E5,+D5:E5,Table296)),{"0.0","hh:mm:ss"}),".","."&REPT(" ",9)),9)))

Created date is Sunday:
18-Jul 10:21 AM, do you need it counted from 0:00 next Monday: 19-Jul 00:00 (With 13h39m deducted)? or same time of next working day, 19-Jul 10:21AM?

Closed on Sat:
17-Jul 9:00 PM, do you need it to be previous working day Fri 16-Jul 23:59:59 (With 9h deducted)? or same time of previous working day Fri 16-Jul 9:00 PM?