Complexed Time Difference Formula

[mahmed1]

Hi All

I have column where i have a start date time and another column with End Date Time

What i want to do is calculate the difference but only the difference between my start shift excluding Sundays

Weekday 08:00 - 21:00
Saturday 09:00 - 15:00

So i need to look at the difference between the 2 values factoring in those shift pattern

eg

Start Date Time…..End Time…..Diff (expected)
02/10/2023 09:00….02/10/2023 10:00…01:00
02/10/2023 18:00….03/10/2022 10:00…04:00
20/10/2023 19:00…23/10/2023. 11:00.. 11:00

20/10 breakdown
Friday 02:00
Sat 06:00
Mon 03:00

 

[duggie33]

Glad to help. To get the time to display hours greater than 24 hours change the custom number format to "[hh]:mm;@".

Doug

 

[mahmed1]

Hey doug - does this mean by changing format i can get desired results so it factors in weekday hours and weekend hours? Or are there still any issues/caveats that it wont account for?

 

[duggie33]

I believe it accounts for everything you asked for...copy and paste the data from post #9 into your Excel and test it with some of your data. On your file, you will need to create the work schedule so it can get the work hours for each day.

Doug

 

[mahmed1]

Thank you

To ignore sundays do i leave the start and end times as blank

 

[duggie33]

Putting False in Workday will work also.

 

[mahmed1]

Thank you
You are a star
Is there any chance you can break down the main bits and how it works just for my own sanity and knowledge

Thank you again

 

[duggie33]

You're welcome. I can try to give you a breakdown...

• the LET function lets you assign variables and then use them in subsequent calculations; the structure is name1, value1, name2, value2, etc...., and it ends with a final calculation to be the result.
• "start" and "end" are the two date-time values you have specified
• "sched" is the work schedule table used for VLOOKUPS later
• "startday" and "endday" return the dates without the times
• "seq" builds a sequence of dates from the start date to the end date
• "wdays" returns the WEEKDAY for each date in "seq"
• "wrkdays" uses the "wdays" and "sched" to determine if each day in "seq" is a Workday (TRUE/FALSE)
• "starttimes" populates the start time for each date in "seq"; the first date (min) uses the start time from your entry with MOD(start,1), else it uses the start of the workday from "sched"
• "endtimes" is similar to "starttimes" in structure
• the final calculation sums the "(endtimes-starttimes)*wrkdays" for each day in "seq"
  • "wrkdays" is the TRUE/FALSE (1/0) from the "sched" table so only TRUE values are part of the sum, FALSE values return 0

One thing I did not check and have not given much thought to is whether the start or end time falls outside of working hours. You might want to do some testing on that if there is a chance it could happen.

Hope that helps,

Doug

 

[mahmed1]

Thank you so much