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?

 

[braindiesel]

The NETWORKDAYS * 28800 is because you only wanted to count the working hours (seconds) if over a full day.
I tested same day start and finish, broad times and 1 min before to 1 minute after.
I didnt test the start and finish being outside of work hours because... how does that happen? you dont count those hours.

 

[AndyTampa]

I understand the NETWORKDAYS*28800, but need to wrap my head around the formulas that multiply by 86400.

I'm also trying to wrap my head around how work created and finished the same day will cause NETWORKDAYS-2 to be a negative number (-57600 seconds), but if the work is created and finished on the same day (let's just say 2 hours or 7200 seconds), the results look correct and are a positive number too. I'm trying to understand the parts of the formula for the start and end day and how they relate if they are the same day.

Work outside of those hours can and has happened under a specific set of circumstances. It is a Call Center environment, so tasks come in 24 hours a day. If overtime is approved and the work arrives after 5:00pm on Friday, and then someone works Saturday or Sunday or both and finishes the job before 9:00am on Monday, it looks like we spent no contract time at all on the task. The contracts only count the mentioned hours to determine if we've met our contractually agreed terms.

 

[braindiesel]

I checked all the scenarios I could think of and it works... so... not sure if you were having a problem or not...

Book2
	A	B	C
1	Day starts	Day ends
2	9:00:00 AM	5:00:00 PM
3
4	Start date	End date	Seconds to complete
5	6/16/2020 8:50	6/16/2020 8:59	0
6	6/16/2020 8:50	6/16/2020 9:01	60
7	6/16/2020 8:50	6/16/2020 17:01	28,800
8	6/16/2020 8:50	6/17/2020 8:50	28,800
9	6/16/2020 8:50	6/17/2020 9:01	28,860
10	6/16/2020 8:50	6/17/2020 17:01	57,600
11	6/16/2020 8:50	6/18/2020 8:50	57,600
12	6/16/2020 8:50	6/18/2020 9:01	57,660
13	6/16/2020 8:50	6/18/2020 17:01	86,400
14	6/16/2020 9:01	6/16/2020 9:02	60
15	6/16/2020 9:01	6/16/2020 17:01	28,740
16	6/16/2020 9:01	6/17/2020 9:01	28,800
17	6/16/2020 9:01	6/17/2020 17:01	57,540
18	6/16/2020 9:01	6/18/2020 8:50	57,540
19	6/16/2020 9:01	6/18/2020 9:01	57,600
20	6/16/2020 9:01	6/18/2020 17:01	86,340
21	6/16/2020 17:01	6/16/2020 17:02	-
22	6/16/2020 17:01	6/17/2020 8:50	-
23	6/16/2020 17:01	6/17/2020 9:01	60
24	6/16/2020 17:01	6/17/2020 17:01	28,800
25	6/16/2020 17:01	6/18/2020 8:50	28,800
26	6/16/2020 17:01	6/18/2020 9:01	28,860
27	6/16/2020 17:01	6/18/2020 17:01	57,600
Sheet1

Cell Formulas
Range	Formula
C5:C27	C5	=(NETWORKDAYS(A5,B5)-2)*28800+IF(A5-INT(A5)>=dayEnd,0,(dayEnd-IF(A5-INT(A5)<=dayStart,dayStart,A5-INT(A5))))*86400+IF(B5-INT(B5)<=dayStart,0,(IF(B5-INT(B5)>=dayEnd,dayEnd,B5-INT(B5))-dayStart))*86400

 

[AndyTampa]

The part of the formula "NETWORKDAYS(A5,B5)-2" subtracts 2 NETWORKDAYS from the total even if there are less than 2 NETWORKDAYS in the span. Then the rest of the formula adds the times for the start and end. The formula assumes they are themselves NETWORKDAYS when they might not be. This is where the errors are. For all of the tests you had done, the start and end were on NETWORKDAYS. For example, if I start a job at 4:00pm on 7/3/2020 and end at 8:00am on 7/4/2020, the result should be 1 hour or 3600 seconds. Your formula gets a value of -25,200 seconds.

I've come up with something based off of what you created.
First I test to see if start and end are on the same day.
If not, I take the total NETWORKDAYS, subtract the start day if it's a NETWORKDAY and then subtract the end day if it's a NETWORKDAY. I then multiply the remaining days by 28800.
Second, I add the number of seconds spent on the start day if it's a NETWORKDAY to the number of seconds spent on the end day if it's a NETWORKDAY and then multiply the sum of those two days by 86400. I use the MIN and MAX functions to calculate each day's seconds individually.

This is the formula I came up with. I really wish it could be shorter. To this I have to add the company holidays so it gets quite long:

=IF(INT($D2)=INT($F2),IF(MOD($F2,1)<0.375,0,NETWORKDAYS($D2,$D2)*86400*(MIN(MOD($F2,1),0.708333)-MAX(MOD($D2,1),0.375))),((NETWORKDAYS($D2,$F2)-NETWORKDAYS($D2,$D2)-NETWORKDAYS($F2,$F2))*28800)+((NETWORKDAYS($D2,$D2)*IF(MOD($D2,1)>0.708333,0,0.708333-MAX(0.375,MOD($D2,1))))+(NETWORKDAYS($F2,$F2)*IF(MOD($F2,1)<0.375,0,MIN(0.708333,MOD($F2,1))-0.375)))*86400)/28800

I'm still tweaking and testing, but this shows much promise. I'm trying to put it in the form of a macro, but after I record the macro, it errors out at typing it back again. I'm using the macro to automatically add the necessary columns to the spreadsheet and type in the range of company holidays for use by the networkdays. Something in the formula appears to be wreaking havoc with VBA.

What do you think?

 

[braindiesel]

I tested all of the scenarios... they all worked...
I don't know why this is still an issue

 

[braindiesel]

Check your data
For example, if I start a job at 4:00pm on 7/3/2020 and end at 8:00am on 7/4/2020, the result should be 1 hour or 3600 seconds. Your formula gets a value of -25,200 seconds.
You only get the - time if you forget to change the date

Book2
	A	B	C
1	ay starts	Day ends
2	9:00:00 AM	5:00:00 PM
3
4	Start date	End date	Seconds to complete
5	6/16/2020 16:00	6/17/2020 8:00	3,600
6	6/16/2020 16:00	6/16/2020 9:00	(25,200)
Sheet1

Cell Formulas
Range	Formula
C5:C6	C5	=(NETWORKDAYS(A5,B5)-2)*28800+IF(A5-INT(A5)>=dayend,0,(dayend-IF(A5-INT(A5)<=daystart,daystart,A5-INT(A5))))*86400+IF(B5-INT(B5)<=daystart,0,(IF(B5-INT(B5)>=dayend,dayend,B5-INT(B5))-daystart))*86400

 

[AndyTampa]

You're missing my point. You didn't check all the scenarios. This work can be done 7 days a week, but we are trying to calculate how much time was spent during the contracted times, which counts only 9:00am to 5:00pm M-F excluding company holidays. The report will be made using actual start and end times and we are determining contract compliance from that.

In your latest example, 6/16/2020 is a Tuesday and is a NETWORKDAY. 6/17/2020 is a Wednesday and is a NETWORKDAY.
Your formula doesn't work if either the Start Day or End Day or both of those are NOT a NETWORKDAY.

Change the dates to Friday 7/3/2020 @ 16:00 and Saturday 7/4/2020 @ 8:00. This is how your formula calculates it:

1 NETWORKDAYS - 2 = -1 NETWORKDAYS times 28800 seconds per 8 hour day = -28800 seconds
If the Start Time (16:00) > dayend (17:00), then 0, otherwise dayend (17:00) minus (if Start Time (16:00) <= daystart (9:00), daystart (9:00), otherwise Start Time))
. since 16:00 is not greater than 17:00 nor less than 9:00, this calculation is 17:00 - 16:00, which = 1 hour or 3600 seconds
If the End time (8:00) <= daystart (9:00), then 0, otherwise (if End Time (8:00) >= dayend (17:00), then dayend (17:00), otherwise End Time (8:00 minus daystart (9:00))
. since 8:00 is less than 9:00, then this day gets 0 seconds.
Add the three up and you get -25200 seconds (-28800 + 3600 + 0)

For the purposes of this report, we have spent 3600 seconds on this job even if we finish it two days later on Sunday. Please check your scenarios again using Weekends to start or finish and you'll see what I mean. I've tweaked the formula I wrote two posts ago, but I'm out of time today. I'll explain the logic behind it shortly. They say the perfect formula doesn't have any IFs in it. I've gotten my formula down to a single IF but I can't get VBA to run it. I haven't fully tested it yet either, but I'm getting there.

 

[braindiesel]

Ok... not plugged in atm but quickly...
change the -2
-2+if(or(weekday(started cell)=1,weekday(started cell)=7),1,0)+if(or(weekday(finished cell)=1,weekday(finished cell)=7),1,0)
Messy doing this on my phone but you should get the gist...
-2 but add 1 if the start day is a 1(Sunday) or 7(saturday)
add a second if statement for the end date.
Give that a shot and see if that solves it.

There is a way to change the start number... count from Monday (1) to Sunday (7)... then you can simplify the if statement to without using OR first cell >= 6... but still the two if statements.
When you start writing the weekday function you should see the options for start date... I just dont have it top of mind or on my phone.

Hope that makes sense

 

[AndyTampa]

The Weekday function won't work either because it doesn't account for holidays. With NETWORKDAYS, I can define a list of company Holidays to exclude and just insert them on a new tab or have a macro add a column with them already typed and use that range in the formula.

I've resolved the NETWORKDAYS problem. The other day I wrote, "I take the total NETWORKDAYS, subtract the start day if it's a NETWORKDAY and then subtract the end day if it's a NETWORKDAY. " I'll explain what I mean.

NETWORKDAYS(A2,A2) tests only cell A2 to see if it's a NETWORKDAY. If cell A2 is a networkday, the answer is 1. If not, the answer is 0. This way I can take the total Networkdays [NETWORKDAYS(A2,B2)] and subtract the start and end days if they are network days like so:

(NETWORKDAYS(A2,B2)-NETWORKDAYS(A2,A2)-NETWORKDAYS(B2,B2))*28800

This eliminates the IF statements as well. Using this same method, I can calculate the seconds between 9am and 5pm and multiply that result by whether or not it's a networkday. Ex.: 3600 seconds multiplied by 1 for a networkday or 0 for a weekend day.

I think I've got the seconds of those two days figured out using MOD, MIN, and MAX as well without the need for IF statements. For the start day, I've got:
0.708333-MAX(MIN(0.708333,MOD(A2,1)),0.375) multiplied by the test for networkdays (NETWORKDAYS(A2,A2) to keep the result or use 0. This takes the smaller of the start time (MOD(A2,1)) or 5:00pm and then uses that result to get the larger of the result or 9:00am. The result of that comparison is then subtracted from 5:00pm to count all the time in between.

The tests
6:00pm start: the smaller of 6pm and 5pm is 5pm. the larger of 9am and 5pm is 5pm. 5:00pm minus 5:00pm is 0 seconds.
8:00am start: the smaller of 8am and 5pm is 8am. the larger of 9am and 8am is 9am. 5:00pm minus 9:00am is 28800 seconds.
4:00pm start: the smaller of 4pm and 5pm is 4pm. the larger of 9am and 4pm is 4pm. 5:00pm minus 4:00pm is 3600 seconds.

I can use a derivative of that for the end day.

The only IF I can't seem to shake is when I have to figure out if the start and end are the same day. I don't want to add the time twice. But even that single day can be multiplied by NETWORKDAYS(A2,A2) to get a 0 or 1 multiplier. I'm also starting to think that we can subtract the start day networkday and add the actual seconds in the same calculation, but then it gets harder for me to explain. It seems to get algebraic and I've forgotten my algebra.

 

[braindiesel]

=if(DATE(START DAY) =DATE(END DAY), value if true, value if false)