Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# calculate total hours between date/time (excluding weekends)

Brass Contributor

# calculate total hours between date/time (excluding weekends)

Hello,

I know that this is pretty easy for this group, but it's escaping me at the moment.  I want to calculate the hours between two fields, but I just want a simple number of hours.  No concatenated labels, not days, just accumulated hours and I need it to exclude weekends.

Example:

11/4/22 5:02 PM    -   11/10/22 10:42 AM   (minus weekends) = # of hours

18 Replies

# Re: calculate total hours between date/time (excluding weekends)

I think this is same as another question:
https://techcommunity.microsoft.com/t5/excel/networkdays-formula/m-p/3693884
= NETWORKDAYS(C2,D2) - 1 + MOD(D2,1) - MOD(C2,1)
and format cells as [h]:mm:ss as you noted.
but again this doesn't take into account any work hours and doesn't account for any start or end dates that are on the weekend.
If the start or end might be a weekend then try:
= NETWORKDAYS(C2,D2) - NETWORKDAYS(C2,C2) - NETWORKDAYS(D2,D2) + 1 + MOD(D2,1) - MOD(C2,1)

EDIT after thinking about this if it starts or end on a weekend then the MOD parts (the hours) shouldn't count either so:

= NETWORKDAYS(C2,D2) - NETWORKDAYS(C2,C2)*(1+MOD(C2,1)) - NETWORKDAYS(D2,D2)*(1- MOD(D2,1) ) + 1

# Re: calculate total hours between date/time (excluding weekends)

Sorry, I didn't even think to include start-end times.

lower / upper ranges for the start of the workday would be 7:00 AM and the end would be 11:00 PM.

# Re: calculate total hours between date/time (excluding weekends)

so should 2022-12-17 10:00PM to 2022-12-18 10:00AM be only 4 hours?
what about starting at 2022-12-17 11:30PM? would that be 3 hours?

# Re: calculate total hours between date/time (excluding weekends)

Just to clarify:
So from 12/17/22 10:00 PM to 12/18/22 10:00 AM would be 4 hours correct.
Our business hours are from 7am - 11pm M-F, so we would not operate at 11:30 PM. If something got sent to us after business hours (not usual, but does happen), at say 11:30 PM on a weekday, I would have it's start time at 7:00 AM the next business day (since no one would be available to process it until that time).

All I'm looking for sadly is the best of both worlds where I want the Networkdays function to calculate the difference between two dates/times (excluding weekends), but I need it in hours, not days.

Does that help?

# Re: calculate total hours between date/time (excluding weekends)

@NeilKloster  so in order to account for every contingency the formula is large but hopefully understandable:

``````= LET(StartDay, C7, EndDay, D7, DayStart, TIME(7,0,0), DayEnd, TIME(23,0,0),
incStart,NETWORKDAYS(StartDay, StartDay),
incEnd,NETWORKDAYS(EndDay,EndDay),
daysInBetween, NETWORKDAYS(StartDay,EndDay) - incStart - incEnd,
hoursInBetween, daysInBetween*(DayEnd - DayStart),
startHours, incStart*MAX(DayEnd - MAX(DayStart,MOD(StartDay,1)),0),
endHours, incEnd*MAX(MIN(DayEnd,MOD(EndDay,1))-DayStart,0),
IF(daysInBetween>=0,
startHours + hoursInBetween + endHours,
if(StartDay<EndDay, MIN(DayEnd,MOD(EndDay,1))-MAX(DayStart,MOD(StartDay,1)),0)))``````

so row 1 is the only thing you need to enter with the start day & end day, and then DayStart and DayEnd are the start and end of the work hours.  After that:

lines 2&3: the incStart & incEnd check if the start/end days are weekdays or weekends,

line 4: daysInBetween finds how many valid workdays NOT counting the 1st or last,

line 5: converts full days to # of work hours,

line 6&7: calculate the # hours on the 1st and last days

line 8: checks if # daysInBetween is valid (i.e. Start Day is > End Day)

line 9: adds hours together in 'normal' cases

line 10: calculates # hours if start Day is same as end Day or gives 0 if start is AFTER end

# Re: calculate total hours between date/time (excluding weekends)

Okay, first and foremost - you are awesome!  The amount of effort you are putting into this to help me is amazing!  TYSM!!

So I put in that amazing formula and I'm getting results, but they are off.  I am including a simplified file example to show you what is going on.  Please ignore the conditional format coloring on column C.

best response confirmed by NeilKloster (Brass Contributor)
Solution

# Re: calculate total hours between date/time (excluding weekends)

@NeilKloster  So there are 2 things happening here:

a) any of the lines with start and end being the same day are 0 because you missed the last line of the equation

b) the numbers look weird because you didn't format them to show [h]:mm

If you want purely hours (i.e. [h]:mm of 1:30 would be 1.5) then just add 24* at the beginning (or *24 at the end) of the equation.

see attached.

# Re: calculate total hours between date/time (excluding weekends)

A shorter formula that works if neither Date/Time Opened not Date/Time Closed will be a weekend day:

=IF(OR(A2:B2=""),"",16*(NETWORKDAYS(A2,B2)-1)+24*(MOD(B2,1)-MOD(A2,1)))

# Re: calculate total hours between date/time (excluding weekends)

Thank you so much! I adjusted it as you stated and it works perfectly!! THANK YOU AGAIN SO MUCH!!

# Re: calculate total hours between date/time (excluding weekends)

Haha!! This is also works EXACTLY like the other one too - I just checked them both. Thank you so much!! Much appreciated everyone!

# Re: calculate total hours between date/time (excluding weekends)

So now the client has come back and asked for holidays to also be excluded.  I know that there is a way to do this, by creating a list of dates, but when I tried it, it didn't work for me.  Any thoughts on how to include this as well?

# Re: calculate total hours between date/time (excluding weekends)

@NeilKloster you should only need to add a list of holidays in the workbook and refer to that list in the NETWORKDAYS formulas.  In the attached I added a sheet with a HOLIDAYS tab I created for one of my companies and you can 'turn on'/'turn off' different holidays and add years to the Year column and the corresponding holiday dates are calculated.

On Sheet1 is both my formulas (output in H:MM vs # hours) and the alternate formula from @Hans Vogelaar and both have been updated with the holidays reference.  Note there are a few output differences between the 2 so use whichever works for you.

# Re: calculate total hours between date/time (excluding weekends)

Appreciate it BTW. @Sorry to ask again - the client came back this morning apologetic, with how they want to track these cases.  The don't want business hours, they only want business days and they explained differently how they want this tracked.  LOL!

So basically, if a case comes in from 6 AM - 7 PM (M-F), it needs to be completed in 1 business day, meaning no later than 7 PM the next business day. The way that I have everything setup now, if I change the time on the Date/Time closed column past 7 PM, everything stays the same.

I think I'm missing something simple, where basically I need to have a column that indicates that the value in the Date/Time Closed is past 7 PM, but I think I've looked at this time enough that I'm blind to it.

Example:

This is correct since the date/time closed is under 7 PM

This is not correct since the date/time closed is past 7PM

It should read 2.00 business days since the date/time closed is past the 7pm cut-off.

I tried doing even just a "-1" on the business days column, which would remove a day, but that doesn't ultimately do anything since I need the formula to be time sensitive to when it was closed.  Adding an attachment sample - any help again is appreciated.  Sorry for constantly asking.

# Re: calculate total hours between date/time (excluding weekends)

so basically you need to 'make' anything after 7PM the next day so simply add 5/24 to force the any time after 7pm to be the next day (the morning hours becoming later that same day don't matter). So simply =NETWORKDAYS(start, end+5/24, holidays)-1 will work ....... ALMOST. The problem will come in when it starts on Th and end after 7pm Fri since that would become Sat and NETWORKDAYS will ignore Sat (or any of the holidays). So if we assume we don't care what day it is, if it is after 7pm then +1 workday then maybe:
=NETWORKDAYS(start, end, holidays) -1 + (MOD(end,1)>19/24)

# Re: calculate total hours between date/time (excluding weekends)

Sort of - so basically it would be the next business day, so if it comes in after Thursday at 7 PM, it is considered Friday's business day.  If it comes in after Friday at 7 PM, then it would be considered Monday's business day (unless it's a holiday).

(So many conditions).  Phew.

To summarize:

6 AM - 7 PM (M-F) Business Hours.

If it comes in between 6 AM - 7 PM on a Monday, then the team has until 7 PM the next business day (Tuesday) to complete it.  If it comes in after 7 PM on a Monday, then it is considered the next business day (Tuesday) and the team has until Weds at 7 PM to complete it.  Does that help?

# Re: calculate total hours between date/time (excluding weekends)

So just looking that file that you sent back, I ran through a few scenarios with weekends and it looks like it works correctly.  I ran through it with just the normal work weeks and also tried it out on a holiday and it seems to work correctly.  But it sounded like you thought that there was another issue?

# Re: calculate total hours between date/time (excluding weekends)

I think the only concern I might have is weekend/holiday issues like coming in and going out the same weekend may result in negative days or coming in on a Th and going out on Sat (before 7pm) may say 1 day. But again if no one is working on the weekend then its ok. but also holidays like columbus day or others that maybe are considered 'holiday' in terms of shipping times but people may still be working. Also didn't account for receiving after 7pm so you need to add -(MOD(start,1)>19/24).
So again if we need to account for every contingency it can get dicey but otherwise it should work.

# Re: calculate total hours between date/time (excluding weekends)

Think I got it all setup (hopefully unless they come back with changes).  Once again, I really, REALLY want to thank you for all your help!  I really appreciate it greatly!

1 best response

Accepted Solutions
best response confirmed by NeilKloster (Brass Contributor)
Solution

# Re: calculate total hours between date/time (excluding weekends)

@NeilKloster  So there are 2 things happening here:

a) any of the lines with start and end being the same day are 0 because you missed the last line of the equation

b) the numbers look weird because you didn't format them to show [h]:mm

If you want purely hours (i.e. [h]:mm of 1:30 would be 1.5) then just add 24* at the beginning (or *24 at the end) of the equation.

see attached.