Calculate elapsed time in days,hours and minutes excluding weekends and holidays

How to compute for elapsed time in days,hours and minutes based on the following conditions?

1. Break is constant 60 mins
2. On Same date, If start time and end time is before 12NN then break should not be deducted
3. On Same date, If start time and end time is after 1PM then break should not be deducted

4. On different dates, If end time is before 12NN then break should not be deducted
5. sample output 22d 5h 45m

On Sheet Time column A2-E2:
2/5/2024-3/7/2024

Start Time End Time Break hh:mm Total hh:mm
9:50 am 5:00 pm 1:00 6:10 ---- 2/5/2024
8:00 am 4:35 pm 1:00 7:35 ---- 3/7/2024

Total Time: 22d 5h 45m

Then, compute for the days in between 2/5/2024-3/7/2024 on the Weekends and Holidays Sheet excluding those
marked as weekend and holidays.

I have attached a sample excel sheet for your perusal.

Thank you.

Are you still using Excel 2010?

Hi there,

You don't need a list with all dates and identify weekends. We only need a table with your public holidays falling on weekdays. We then can use the NETWORKDAYS formula.
See attached file.
In column 'I' we calculate the full days (-1 so we don't count start and end date and double up).
In column 'J' we calculate the hours for the first/last day, less the 1 hour break if starting before 12 noon and finishing after 1pm)
Finally in column 'G' we show the result in day(s), Hours & Minutes.

Hope this helps.

Update your forum profile NOW, please.

If you have a workspan over a period of time, how do you know if the individual did work on a specific holiday or weekend?

You could use in cell 'K2' (and pull down) something like this:

Formula:

=IF(COUNTIFS('Weekends and Holidays'!$A$2:$A$20,">="&Time!A2,'Weekends and Holidays'!$A$2:$A$20,"<="&Time!C2)>0,"Yes","No")

This will tell you IF there is at least one of the listed public holidays in the time span for each row.


Or use the formula below to check how many holidays fall into the period;

Formula:

=COUNTIFS('Weekends and Holidays'!$A$2:$A$20,">="&Time!A2,'Weekends and Holidays'!$A$2:$A$20,"<="&Time!C2)

Look at the screenshot.

Hi there,

Please refer to the updated file.

Assuming that the first day may be a weekend or holiday day, we can work the hours as additional hours to the normal hours (hours in columns 'G' & 'H' are additional to the days/hours in column 'F'.
If the delivery day is a weekend day and a holiday, we count that day as a holiday (not weekend). This may does not make a difference if your overtime rates are the same for weekends and holidays.
Assumption is that deliveries may arrive on a weekend or holiday day. Weekends and holidays after the delivery are not worked.

To make the template more flexible, I have added some variables in columns 'Q' and 'R'. There you can adjust the default start and end times as well as the lunch break hours.

Suggest you add some more examples and validate the results from the formulas match your expectations.

Trust this helps.

Thank you for your assistance. What I meant is that work rendered on holidays are not considered overtime, hence, the output should be the same with regular days. The only thing that I need to do is to indicate the dates of Weekends or Holidays where deliveries are made on the Weekends and Holidays sheet and it would be treated as a regular day. No need to have a separate column for overtime.

Ok, all hours are 'normal' hours and included in one column 'F'.
If the first day (delivery) was a weekend day, we indicate this in column 'G', if it was a holiday, we indicate in column 'H'.

I have also simplified the Holiday register a bit. All you need to do is add the date. The weekday and if it is a weekend day will be populated by formulas.

Hope this helps.

You have negative values because the end time is earlier than the start time. This happens when shifts span midnight.

Try this:

=((C2+D2)-(A2+B2)) -IF(AND(B2<lStart,D2>lEnd),lBreak,0)

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

You'd need to test that yourself.

I tweaked the time formula in column K - there is nothing in that relating to holidays or weekends. So which formula is not doing what you want? And who provided it?

So which formula do you need help with? Which column? Try to be more precise with your answers.

Explain the purpose of the YES and NO entries in the holidays lookup sheet. Are you saying the weekends in JANUARY are working days, but that in any other month they are not? You cannot reverse days being excluded as weekends by NETWORKDAYS by adding them to the holiday list.

It's all very confusing - you need to explain the set-up of your workbook fully. It's all too vague at the moment.

Hi AliGW,
Thanks for stepping in. Busy weekend here.
Agree, some bits are confusing. I tried to get my head around it (and will give it some more time).

Kraddark, can you please confirm:

Basically what I understand is;
- Time between Start and end dates/time.
- Exclude weekends and holidays (which the networkdays function should take care of.
- There is no need to add weekends into the table as the networkdays formula will deduct them, automatically. We only need to add the holidays to be excluded as such.
- But IF the first day is a weekend day or holiday day, then that day needs to be included (Plus 1 day to the networkday calc..)
- IF the delivery (first day) is on a Saturday, then only the Saturday is counted, not the whole weekend.

There were also some strange start times way out of the ordinary work hours (eg; 11pm). IS this really the case, or should the start end times fall withing ordinary work hours set in the variables (around columns Q & R).

Also, if you have a new/updated sample file, please upload the new file in your replay. Don't keep changing the file in your original upload.

Thanks

...whilst waiting for confirmation of actual requirements;

Please refer to the attached file.

Columns F to I are not required, just for visualization and can be deleted.

Not sure what you mean by "all weekends in January are considered workdays". What about the weekends in February, March . . December?

In the attached version, we count Mon-Fri, less holidays.
If the delivery (start date is on a weekend or holiday, we count the start time to the default end of workday hours.
I noticed you have one row with the END date being on a weekend. What are the rules there?
Some START hours are very early like 2:45am, some as late as 11:00pm. Are you operating 24 hours, or are the sample times not reflecting actual scenarios.

Regards

Hi, I understand that networkdays takes care of the weekends but the thing is there are also times when deliveries are made on a weekends like in January where all weekends have deliveries too. I want to make the worksheet flexible by indicating weekend dates that have deliveries to be included in the count of working days. I have included in the results sheet the expected results based on start and end dates/times. Perhaps another list that will show which weekends should be included in the workdays count or is there another workaround for that?

Please have a look at the 'Testing' sheet (updated file attached).

I have visualised the first 3 rows of your 'Result' sheet.
For the first row, we have a variance of 4 days. I take it you included the 2 weekends as worked, added in bold red.
The other 2 sample rows do match. This means the weekends for these rows are not counted as worked.

There is no indication in the raw data in which scenario the weekends are worked or not worked.
I can't see any logic to get a formula to pick up which row includes weekends and which rows don't.

Regards,

Sorry not clear.

Look at the first 3 samples from your 'RESULT' sheet.
I visualized them on the 'Testing' sheet.

Sample from row 1: Column D - 4 days difference because you want to include 2 weekends in January.

Sample from row 2: Column E - No difference because you are NOT including the weekend in January.

Sample from row 3: Column F - No difference because you are NOT including the weekend in January.

Basically, sometimes you include the weekend in January and sometimes you are not. Why do you treat samples in row 2 & 3 different from the one in row 1 ?


You would need an additional column on the 'Time' sheet to manually indicate if you want to include all weekends/holidays for that specific row or not.
Or you could have a column where you manually add the weekend/holiday days for that specific row, which then would be added to the automatic calculation.

Hi again,

You wrote: 'I declared all weekends in January as part of the work days except for January 27-28.'

This is not correct for the first sample on the 'Result' sheet. The 4 days difference between the start & end date are 20/21 & 27/28 January. You include 27/28 in this sample.


In order to include/exclude certain weekends/holidays for an individual row (but not for others (such as row 2 & 3 from your 'Result' sheet), you need to have a column in your 'Time' sheet to indicate this. Please see my previous reply.

See attached v4;
In the 'Time' sheet, I inserted a column for you to manually add the number of weekend/holiday days you want to include for each specific row.

IF you don't want to manually count and insert the number of weekend/holiday days to be included, AND all weekends/holiday days in a specific row are always included, we could use a 'Y' / 'N' flag. That would only work if you only want to include the first weekend, but not the second. You have to revert back to the manual calculated number of days to be added.



On the 'Time' sheet in column 'U', you can change the work hours from 8-5 to 7-4 (or any other time on an overall level (applying to all rows.
If you want to set this for individual rows and not the rest, we again would need an additional column in the 'Time' sheet to indicate the alternative start/end hours to be used.


Regards

Kindly check the attached sheet. It is not giving the correct result for F2 for some reason.

Thank you.

Time Calculator v4.xlsx

Changing the formula in cell Q2 and down will yield the expected result for cell F2:

Formula:

=IF(A2=C2,0,IF(NETWORKDAYS(A2,C2,'Holiday Register'!$A$3:$A$999)-1<=1,0,NETWORKDAYS(A2+1,C2-1,'Holiday Register'!$A$3:$A$999)))+IF(N2>DefHrs,1,0)

The change keeps all other results the same with the exception of F16, which I believe is incorrect and should be 6 hours, however I will wait for confirmation of that before investigating further.
Let us know if you have any questions.

Hi JeteMc,
Thanks for correcting the formula in column 'Q'.

I think F16 is correct with 1 day, 2 hrs, 19 min:
First day (Fri 26 Jan) 6:00 hrs + last day (Sat 27 Jan) 4:19 hrs = 12:19. = 1 day (at 8:00) + 2:19

The OP stated that weekends and holidays are excluded, unless there is a delivery. If there are actual hours on the end date being a saturday, then I think it should be included. But yes, lets wait for confirmation.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.