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]

Try the NETWORKDAYS function
=NETWORKDAYS(start_date,end_date,holidays)
start_date Cell with start date ex B2
end_date Cell with end date ex C2
holidays = Range of cells where you list all dates you want excluded... holidays, company holidays etc ex E2:E15

This formula will exclude all Saturdays and sundays and any dates in your holidays range
The holidays range is an optional part of the function.

Do you need precision of how many hours? or does this solve it?

 

[AndyTampa]

Thanks, but this is not nearly precise enough. When we calculate the number days we work on the task, it is by the second. Weekends and holidays are excluded, but we also exclude all seconds between Midnight and 9:00am and all seconds between 5:00pm and Midnight.

There are 86400 seconds in a 24 hour day, but for our calculation, every whole day is only 28800 seconds long (8 hours).

For example, if the start is 1:54pm on 3/5 and the end is 8:00am on 3/7, we would need to calculate the following:
3/5 = the number of seconds between 1:54pm and 5:00pm (unless it's a holiday or weekend)
3/6 = 28800 for a full day (unless it's a holiday or weekend)
3/7 = no seconds because we finished before 9:00am

Then divide the sum by 28800 (full day of seconds) to come up with the number of days taken to complete the task.

Somehow I need to first determine if the start day is a weekend or a work holiday, then calculate how many seconds on that day we worked on the task. Then I have to add full days that aren't a weekend or holiday. Next, I have to determine if the end date is a weekend or holiday before calculating how many seconds we worked on the task. Finally, I can add up the sums and divide by 28800 to get the resulting number of days. NETWORKDAYS will certainly come into play, but a lot more is needed.

 

[braindiesel]

OK, so use the Network days as previously said multiplied by 28800... that captures all of the days in the range at the seconds level.
Now we have to do a little bit of math to adjust for the starting and ending point...

For the Starting adjustment
=(B1-B2)*86400-IF(B2<0.5,3600,0)
Where B1 = 5:00:00 PM
Where B2 = Starting time
This part... (B1-B2)*86400... calculates the seconds of the day on that date
This part...-IF(B2<0.5,3600,0).... deducts 3600 seconds if start is before 12:00:00 PM to account for lunch

For Ending Adjustment
=(B2-B4)*86400-IF(B2>0.5,3600,0)
Where B2 = 8:00:00 AM
Where B4 = Ending Time
I used the same math logic for the lunch hour... you may have to play with this if there is the possibility of a job finishing between 12-1p.m.

so...
=(NETWORKDAYS formula from above - 2) *28800+ starting adjustment formula + Ending Formula
Notice the -2 on the Network days formula as we want to remove the first and last day before adding back the partial amounts with your starting and ending adjustment formulas

How is that...

 

[AndyTampa]

Wow. That was fast. I'm about to clock out for the day, so I won't have a good chance to look at it until Monday (or later if they assign something else). The first thing I'd like to say is that lunchtime is not excluded. The clock ticks as long as the task is still unfinished, regardless of lunch.

For the Starting adjustment
where B1 = 5:00:00 PM --- there is no cell for that 5:00 pm. This 5:00pm must be part of the formula that calculates how many seconds were part of the task on that day.
The calculations don't appear to account for the excluded times.
Examples:
If the start time is 7:00am and runs to 6:00pm, that would be 28800 seconds for a full day. The same would be if the start time was 7:00am and it ran into the next day.
If the start time is 7:00am and runs until 1:00pm of the same day, we need to calculate the seconds from 9:00am to 1:00pm. This is difficult because the end time is from a different cell. I think we need a different calculation if the start and end are on the same day.
If the start time is 3:00pm and runs until the next day, we need to calculate the seconds from 3:00pm to 5:00pm for the start day.

(B1-B2)*86400 doesn't appear to calculate a fraction of the 86400 seconds in a day (or I'm not understanding how the B1-B2 is going to give me a fraction of 86400. It also doesn't consider that 5:00pm minus 7:00am would be too many seconds since time doesn't start until 9:00am.

I understand how NETWORKDAYS-2 counts only the days in between and I know that I can use NETWORKDAYS(A1,A1,Holidays)*[the seconds calculation] for both the first and last days to exclude those days if they fall on an excluded day. I'm stuck trying to figure out the valid seconds actually used on the first and last day. I'm considering the several IF type functions (countif, sumif, etc.), but I don't use them enough to know them.

If the start is before 9:00am, the valid seconds would be 28800 if it takes all day. If the start is after 5:00pm, the valid seconds would be 0. If the task is completed the same day, I'd have to somehow calculate the seconds while including the end date/time from the ending time cell.

Likewise, it is just as difficult, if not more so, to calculate the difference between two date/time serial numbers, converting that to seconds, subtract the standard 5:00pm to 9:00am seconds between network days, and still have to calculate the actual seconds for the start and end days.

Thanks for helping. Anything that moves me closer to the goal is very much appreciated. Have a nice weekend, and thank you.

 

[braindiesel]

I will be happy to tackle this but afk for the moment
To give you a final answer please let me know the following...
1) The cells that have the start and end time... What is in them... click on one and format as a comma style (button with comma in middle of home ribbon)
I need to see how the value is stored...
Is it a time (0.25)
Is it a date and time (42786.25)... that sort of thing
That will help me resolve this

 

[AndyTampa]

I really do appreciate the help. I'm not sure what you are requesting for that data. I look at the column and see date/time stamps. When I click on the comma style button to which you referred, the date/time converts into a number with 2 decimal places. For that reason, and maybe this is more than you needed, I've extended the results to 10 decimal places. The Created date shows as "4/28/2020 13:01", but if I select the cell, it shows up in the Formula Bar as "4/28/2020 1:01:20 PM". In Comma Style to 10 decimal places, it shows "43,949.5425925926".

Your question also made me realize that it may be a little more complicated. She is expecting to have a formula that she can edit to use on multiple reports, each for different levels of service. Since I don't have access to the reports, I don't know which columns have the start and end dates/times on each report. I am fairly certain that they'd be formatted similarly with the same column headers. It is possible to give my sup a formula and then tell her to change this cell reference or that cell reference to accommodate each report. However, it would be fantastic to use the unique column title in the formula. The columns are titled "Created" and "Completed".

I can provide her a worksheet with the company holidays that she'll have to attach to each report so that the NETWORKDAYS function will be able to access the list, unless that can be made part of the formula too. I thought I could figure this out on my own, but it's starting to seem wayyyyy beyond my depth. It's starting to sound like a job for a personal macro that she can save and run on each report that will add columns for the holidays and formulas (possibly A & B) after asking which columns have the start and end points. But that's not just beyond my depth; it's in a different pool.

 

[AndyTampa]

BTW, I don't know if this will make any difference, but I am using Excel 2010. I don't know if my supervisor is using a newer version on her company-supplied laptop.

 

[braindiesel]

I only asked because I needed to know what was in the cell and I was not getting the right information...
I do not see why this would not work in Excel 2010 and beyond
I believe I have this solved here

Book3
	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 3:27	7/16/2020 15:27	656,832.00
6	6/16/2020 9:00	6/16/2020 9:01	60.00
7	6/16/2020 20:27	6/18/2020 15:27	52,032.00
8	6/16/2020 3:27	6/16/2020 9:01	60.00
9	6/16/2020 13:20	6/16/2020 13:30	600.00
10	6/16/2020 21:59	6/17/2020 9:01	60.00
11	6/16/2020 16:59	6/17/2020 9:01	120.00
Sheet1

Cell Formulas
Range	Formula
C5:C11	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

Named Ranges
Name	Refers To	Cells
dayend	=Sheet1!$B$2	C5:C11
daystart	=Sheet1!$A$2	C5:C11

 

[AndyTampa]

That is a long one. I like it. I'm evaluating it, but have run out of time today. I made some changes that I don't think affect the result. Please tell me if you see a problem.

I can't ask my supervisor to add Named Ranges every time she runs a report, so I changed every dayend to 0.708333333 and every daystart to 0.375. For getting the times from the cells, I tried using MOD(A5,1) for every A5-INT(A5). It doesn't change the length of the formula, but your way is easier to understand the flow.

I don't yet understand how it works. If the start and end are on the same day, the first part of the formula comes up with a negative result, so I'm perplexed how it comes up with the right answer. Likewise, if I start on 7/4/20 @ 9:01am and end on 7/6/20 @ 8:59am, the result is -60 seconds; equivalent to 0 for my purposes. Similarly, if I go from 9:05am-12:34pm on 7/5/20, I've worked -16260 seconds.

I haven't figured out yet why the NETWORKDAYS was multiplied by 28800 seconds, but the start and end days multiply by 86400 seconds.

What I'm going to need to add is a way for the formula to recognize which columns by the column header. I think that's going to have something to do with FIND or MATCH or INDEX or something like that for every instance of A5 or B5. I also need to convert the result to DD:HH:MM:SS spent working on the task where DD is the number of 8 hour days.

This is a phenomenal start. Thank you very much!! I'll post again once I've had a chance to fully understand it. That might be a couple of days, depending on work priorities.

It might be challenging after I get this to her to eventually create a macro that automatically adds two columns to the beginning of the worksheet and adds the company holidays and formulas. That would be fun.