Working Time With Varied Day Lengths, Excluding Weekends & Holidays

[st360]

Hi
I would really appreciate some help with the following:

I have a date range (example: 1 January 2021 09:14 to 28 February 17:23)

excel1.JPG


I would like to understand how I can get the elapsed time in dd-hh-mm under the following working times:

Mondays 6am - 10pm
Tuesdays 6am - 10pm
Wednesdays 6am - 10pm
Thursdays 6am - 10pm
Fridays 6am - 4pm

Additionally, exclusions would be weekends and selectable holidays


Many thanks for your support
Steve

[Keebellah]

Hi, time calculations are tricky
For starters you can calculate the days using NETWORKDAYS() function where you can include the holidays to exclude, that will give you all the weekdays excluding weekends and holidays
Next you will need to do some calculating for how many weeks that is, it will be rough but every weekday is 10 pm - 6 am (in real time numbers) except for the fridays which end a 4 pm
Every completed week is the sum of all these hours.
You do this for the second day untill the day before that last date
Then you add the time worked on the starting date in your case if it's not a friday 22:00 - 09:14 and the last day is 22:00 - 17:23
Attach a file with non private data if you want more help
This is just to give you the idea

[kvsrinivasamurthy]

Pl read yellow banner on top of this page.

[st360]

Hi
Attached is an example worksheet

Thank you

[Keebellah]

Will take a look later and update, am on-the-road now

[st360]

Great, thank you

[kvsrinivasamurthy]

Pl see file with formulas.
For days

=IF(AND($A2=$C2,$D2-$B2=TIMEVALUE("16:00:00")),1,0)+MAX(0,NETWORKDAYS($A2+1,$C2-1,$B$8:$B$10))

For hours

=INT(24*(IF($A2=$C2,$D2-$B2,IF(WEEKDAY($A2,2)<6,"22:00","16:00")-$B2)+(MIN(IF(WEEKDAY($A2,2)<6,"22:00","16:00"),$D2)-"6:00")))

For minutes

=MOD((IF($A2=$C2,$D2-$B2,IF(WEEKDAY($A2,2)<6,"22:00","16:00")-$B2)+(MIN(IF(WEEKDAY($A2,2)<6,"22:00","16:00"),$D2)-"6:00"))*24,1)*60

[st360]

Hi kvsrinivasamurthy

Many thanks for taking the time to have a look at this
Unfortunately, the days are not counting correctly, the hours are going over 24 and hours are increasing over the weekend when it should be ignored

Again, thank you
Steve

[kvsrinivasamurthy]

Give the expected answer in the file. Give the split figures how you calculate manually.
Say Start day 15 hrs and end day has 10 hrs. Totally it is 25 hrs. Starting is Tuesday and end day is Saturday how you calculate hrs and days.

[Keebellah]

Does this look like you expected?
I need to add a check for the last day if it's a Friday or not

[st360]

Hi Keebellah

Would it help if I kept this simpler and we just summed the number of elapsed hours
In the attached spreadsheet, I have shown how I achieve the result

Many thanks
Steve

[Keebellah]

The result looks great if this is what you ned

[kvsrinivasamurthy]

For Monday you have taken as 840 Mins. Actually 6am to 10 pm ,!6 hrs. 16*60=960 Mins. How it is 840Mins?

[kvsrinivasamurthy]

Taking Working Hrs As 16 hrs, Formula is

Please Login or Register  to view this content.

[st360]

Guys, this is perfect for me. I really do appreciate your help and can't describe how grateful I am

[kvsrinivasamurthy]

Thanks for feedback. Pl mark the thread solved.