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

[AndyTampa]

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]

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.

 

[braindiesel]

or...
=if(INT(START DAY) =INT(END DAY), value if true, value if false)
would solve it.

 

[AndyTampa]

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
	A	B	C	D	E	F
1	START	END	HOURS	X3600	Formula	Contract Days
2	ONE NETWORK DAY
3	7/1/2020 8:00	7/1/2020 8:30	0	0	0	0
4	7/1/2020 8:00	7/1/2020 10:00	1	3600	3600	0.125
5	7/1/2020 8:00	7/1/2020 18:00	8	28800	28800	1
6	7/1/2020 10:00	7/1/2020 11:00	1	3600	3600	0.125
7	7/1/2020 10:00	7/1/2020 18:00	7	25200	25200	0.875
8	7/1/2020 18:00	7/1/2020 19:00	0	0	0	0
9	TWO NETWORK DAYS
10	7/1/2020 8:00	7/2/2020 8:30	8	28800	28800	1
11	7/1/2020 8:00	7/2/2020 10:00	9	32400	32400	1.125
12	7/1/2020 8:00	7/2/2020 18:00	16	57600	57600	2
13	7/1/2020 10:00	7/2/2020 11:00	9	32400	32400	1.125
14	7/1/2020 10:00	7/2/2020 18:00	15	54000	54000	1.875
15	7/1/2020 18:00	7/2/2020 19:00	8	28800	28800	1
16	THREE NETWORK DAYS
17	7/1/2020 8:00	7/3/2020 8:30	16	57600	57600	2
18	7/1/2020 8:00	7/3/2020 10:00	17	61200	61200	2.125
19	7/1/2020 8:00	7/3/2020 18:00	24	86400	86400	3
20	7/1/2020 10:00	7/3/2020 11:00	17	61200	61200	2.125
21	7/1/2020 10:00	7/3/2020 18:00	23	82800	82800	2.875
22	7/1/2020 18:00	7/3/2020 19:00	16	57600	57600	2
23	ONE NETWORK DAY / ONE WEEKEND DAY
24	7/3/2020 8:00	7/4/2020 8:30	8	28800	28800	1
25	7/3/2020 8:00	7/4/2020 10:00	8	28800	28800	1
26	7/3/2020 8:00	7/4/2020 18:00	8	28800	28800	1
27	7/3/2020 10:00	7/4/2020 11:00	7	25200	25200	0.875
28	7/3/2020 10:00	7/4/2020 18:00	7	25200	25200	0.875
29	7/3/2020 18:00	7/4/2020 19:00	0	0	0	0
30	TWO NETWORK DAYS / ONE WEEKEND DAY
31	7/2/2020 8:00	7/4/2020 8:30	16	57600	57600	2
32	7/2/2020 8:00	7/4/2020 10:00	16	57600	57600	2
33	7/2/2020 8:00	7/4/2020 18:00	16	57600	57600	2
34	7/2/2020 10:00	7/4/2020 11:00	15	54000	54000	1.875
35	7/2/2020 10:00	7/4/2020 18:00	15	54000	54000	1.875
36	7/2/2020 18:00	7/4/2020 19:00	8	28800	28800	1
37	SAME WEEKEND DAY
38	7/4/2020 8:00	7/4/2020 8:30	0	0	0	0
39	7/4/2020 8:00	7/4/2020 10:00	0	0	0	0
40	7/4/2020 8:00	7/4/2020 18:00	0	0	0	0
41	7/4/2020 10:00	7/4/2020 11:00	0	0	0	0
42	7/4/2020 10:00	7/4/2020 18:00	0	0	0	0
43	7/4/2020 18:00	7/4/2020 19:00	0	0	0	0
44	TWO WEEKEND DAYS / ONE NETWORK DAY
45	7/4/2020 8:00	7/6/2020 8:30	0	0	0	0
46	7/4/2020 8:00	7/6/2020 10:00	1	3600	3600	0.125
47	7/4/2020 8:00	7/6/2020 18:00	8	28800	28800	1
48	7/4/2020 10:00	7/6/2020 11:00	2	7200	7200	0.25
49	7/4/2020 10:00	7/6/2020 18:00	8	28800	28800	1
50	7/4/2020 18:00	7/6/2020 19:00	8	28800	28800	1
Sheet5

Cell Formulas
Range	Formula
D45:D50,D38:D43,D31:D36,D24:D29,D17:D22,D10:D15,D3:D8	D3	=C3*60*60
E45:E50,E38:E43,E31:E36,E24:E29,E17:E22,E10:E15,E3:E8	E3	=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:F8	F3	=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]

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]

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]

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]

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]

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]

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]

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.