Calculate time between timestamps excluding weekends, holidays, and certain hours

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122
I've been asked to come up with a formula that my supervisor can paste into some reports to calculate if we've met our contractual agreements. I'm having difficulty even deciding where to begin. She gets reports with two timestamps for start and finish. We need to calculate how many days it took to complete, but they aren't calendar days. The days considered are down to the second. But there are also exclusions.

1) The days are 8 hour days from 9:00am to 5:00pm. Work outside those times is excluded from the calculation.
2) We can't count weekends.
3) We can't count company holidays.

The reports have different limits (i.e. 3 business day, 5 business days, etc.). We're looking for a formula that can simply be pasted into the report and edited for the number of days as needed.

As I said, I can't decide on a starting point. Would I calculate the total seconds using networkdays and subtract the exluded times? That sounds reasonable while still complicated. Does anyone have any ideas I can pursue?
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122
The DATE function requires 3 parameters for Year, Month, Day. I was trying to get rid of the IF function and I think I may have done that by multiplying the equation with a TRUE/FALSE function. Using everything I've written before, this is what I've come up with so far:

=ROUND((INT($A2)=INT($B2))*NETWORKDAYS($A2,$A2)*(MAX(MIN(MOD($B2,1),0.708333333),0.375)-MAX(MIN(MOD($A2,1),0.708333333),0.375))*86400+(INT($A2)<>INT($B2))*(NETWORKDAYS($A2,$B2)*28800-NETWORKDAYS($A2,$A2)*(28800-((0.708333333-MAX(MIN(0.708333333,MOD($A2,1)),0.375))*86400))+NETWORKDAYS($B2,$B2)*(28800-((MIN(MAX(MOD($B2,1),0.375),0.708333333)-0.375)*86400))),0)

Preliminary testing found some errors and I'm out of time to test it fully today. I'll post again once I've tested it.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
or...
=if(INT(START DAY) =INT(END DAY), value if true, value if false)
would solve it.
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122
I had that test before, but I was trying to do the entire formula without IF statements. I remembered something I had read about formulas being better without IFs. But using (A=B) to get a TRUE or FALSE is the same thing. The error in the above formula is a plus sign instead of a minus sign.

This is what I've got and it appears to test out too.

Book2.xlsx
ABCDEF
1STARTENDHOURSX3600FormulaContract Days
2ONE NETWORK DAY
37/1/2020 8:007/1/2020 8:300000
47/1/2020 8:007/1/2020 10:001360036000.125
57/1/2020 8:007/1/2020 18:00828800288001
67/1/2020 10:007/1/2020 11:001360036000.125
77/1/2020 10:007/1/2020 18:00725200252000.875
87/1/2020 18:007/1/2020 19:000000
9TWO NETWORK DAYS
107/1/2020 8:007/2/2020 8:30828800288001
117/1/2020 8:007/2/2020 10:00932400324001.125
127/1/2020 8:007/2/2020 18:001657600576002
137/1/2020 10:007/2/2020 11:00932400324001.125
147/1/2020 10:007/2/2020 18:001554000540001.875
157/1/2020 18:007/2/2020 19:00828800288001
16THREE NETWORK DAYS
177/1/2020 8:007/3/2020 8:301657600576002
187/1/2020 8:007/3/2020 10:001761200612002.125
197/1/2020 8:007/3/2020 18:002486400864003
207/1/2020 10:007/3/2020 11:001761200612002.125
217/1/2020 10:007/3/2020 18:002382800828002.875
227/1/2020 18:007/3/2020 19:001657600576002
23ONE NETWORK DAY / ONE WEEKEND DAY
247/3/2020 8:007/4/2020 8:30828800288001
257/3/2020 8:007/4/2020 10:00828800288001
267/3/2020 8:007/4/2020 18:00828800288001
277/3/2020 10:007/4/2020 11:00725200252000.875
287/3/2020 10:007/4/2020 18:00725200252000.875
297/3/2020 18:007/4/2020 19:000000
30TWO NETWORK DAYS / ONE WEEKEND DAY
317/2/2020 8:007/4/2020 8:301657600576002
327/2/2020 8:007/4/2020 10:001657600576002
337/2/2020 8:007/4/2020 18:001657600576002
347/2/2020 10:007/4/2020 11:001554000540001.875
357/2/2020 10:007/4/2020 18:001554000540001.875
367/2/2020 18:007/4/2020 19:00828800288001
37SAME WEEKEND DAY
387/4/2020 8:007/4/2020 8:300000
397/4/2020 8:007/4/2020 10:000000
407/4/2020 8:007/4/2020 18:000000
417/4/2020 10:007/4/2020 11:000000
427/4/2020 10:007/4/2020 18:000000
437/4/2020 18:007/4/2020 19:000000
44TWO WEEKEND DAYS / ONE NETWORK DAY
457/4/2020 8:007/6/2020 8:300000
467/4/2020 8:007/6/2020 10:001360036000.125
477/4/2020 8:007/6/2020 18:00828800288001
487/4/2020 10:007/6/2020 11:002720072000.25
497/4/2020 10:007/6/2020 18:00828800288001
507/4/2020 18:007/6/2020 19:00828800288001
Sheet5
Cell Formulas
RangeFormula
D45:D50,D38:D43,D31:D36,D24:D29,D17:D22,D10:D15,D3:D8D3=C3*60*60
E45:E50,E38:E43,E31:E36,E24:E29,E17:E22,E10:E15,E3:E8E3=ROUND(IF(INT($A3)=INT($B3),NETWORKDAYS($A3,$A3)*(MAX(MIN(MOD($B3,1),0.708333333),0.375)-MAX(MIN(MOD($A3,1),0.708333333),0.375))*86400,NETWORKDAYS($A3,$B3)*28800-NETWORKDAYS($A3,$A3)*(28800-((0.708333333-MAX(MIN(0.708333333,MOD($A3,1)),0.375))*86400))-NETWORKDAYS($B3,$B3)*(28800-((MIN(MAX(MOD($B3,1),0.375),0.708333333)-0.375)*86400))),0)
F45:F50,F38:F43,F31:F36,F24:F29,F17:F22,F10:F15,F3:F8F3=E3/28800


I know I'm going to change all the Networkdays to include a Holiday list, but now I've got to get it to work from a macro because this is too large to expect someone to type in every day without making a mistake. My attempts at having the macro type it into a cell cause the macro to fail so far.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
I have never seen anyone advise against using If statements.
I have used them for years... easy to read when you know and never an issue...

Embrace the IF function
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122

ADVERTISEMENT

In the past, I have created formulas with so many IF statements that the formula wouldn't function. I don't know if it's still the case, but there was a limit to how many IFs could be nested. Even recently, I found a post that IFs are EVIL, but without any real explanation.

Is there a limit to how long a line can be in VBA? When I tried recording the macro by typing in the formula, the formula wraps in the VBA editor and the macro fails when I try to run it. This will only get worse when I try to type in the macro including the Holiday cells in the networkday functions.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
The old limit of IF statements was 7 in one... not certain if that has changed... but with the new IFS function or using AND or OR in your logical statement there are definitely ways to do it.
If you are finding yourself hitting too many layers, there is probably a better formula for that situation like a VLOOKUP or INDEX/MATCH forexample... but whatever site told you IF is evil... scrub it from your mind... IF is one ofTHE most powerfunctions because you outsource the logical thinking to the computer... do not be afraid of it.

Other than your believe that the IF was a bad idea (it isn't) was there a problem with the formula I gave you...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

the new IFS function
I would recommend against using it, especially if it uses complex/array formulae. The new IFS function calculates absolutely everything & then looks to find the first criteria that is true.
I think the limit for nested IF functions is now 64.
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
I would recommend against using it, especially if it uses complex/array formulae. The new IFS function calculates absolutely everything & then looks to find the first criteria that is true.
I think the limit for nested IF functions is now 64.
I have not felt a need for IFS yet, so thanks for that info Fluff.

Do you knowof any reason someone should not use IF though... I see no reason, but curious your take as Andy had expressed concerns
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
I see no reason not to use nested IF statements, although there are often better ways of doing it.
It maybe the OP had seen something about IFS being bad, rather than nested IF statements
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
122
Fluff, I have not read anything about IFS and have not heard of it until now.

Braindiesel, your formula initially did not work if the begin date or the end date were not on a network day. So you provided a nested IF solution that tested for weekdays and added 1 to the networkdays as long as the start or end were not Saturday or Sunday. The WEEKDAY function does not allow for Holidays. It did provide the inspiration to change the test to NETWORKDAYS(A1,A1) for the start and end days. This would provide a 1 or 0 which could then be multiplied by the number of seconds for each start or end day. The formula will calculate the seconds used on those days and then provide them to the formula only if it is a network day. This turns out to be simpler than the "If this use this formula, otherwise use a different formula" method. I think this might be a good example of what Fluff said about "better ways of doing it".

However, this further inspired the idea that I could take the total number of network days and subtract the number of minutes remaining in the day IF it's a network day. My only hitch was if the first and last day were the same day. I would be subtracting those minutes twice, so I finally needed an IF statement to use a separate formula for same day start and end.

Now I'm free to add the third parameter to the NETWORKDAYS function. I am still stuck getting it to type in a macro though. I have some priority projects that must be addressed first.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,903
Messages
5,638,899
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top