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

#### AndyTampa

##### Board Regular
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
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### braindiesel

##### Well-known Member
or...
=if(INT(START DAY) =INT(END DAY), value if true, value if false)
would solve it.

#### AndyTampa

##### Board Regular
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
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

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

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
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
I see no reason not to use nested IF statements, although there are often better ways of doing it.

#### AndyTampa

##### Board Regular
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.

Replies
3
Views
221
Replies
26
Views
1K
Replies
9
Views
862
Replies
6
Views
230
Replies
3
Views
162

1,132,701
Messages
5,654,812
Members
418,155
Latest member
demasisi

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

### Which adblocker are you using?

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

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