Tricky date and time formula

[sterling_archer]

Hello,

Normally I would just solve this, but it is a bit more tricky then anticipated. Hopefully someone out there knows a best practise.

The summary: I need to calculate the minutes between two dates. However it is depending on weekday and time of day.
Data variables: Mon-Fri & times 08-18/18-23/23-08 | Sat 00-08/08-17/17-24 | Sun 00-00
Cells: start date, start time, start time+start date (text), end date, end time, end date+end time (text)


In example:
startDate weekDay startTime endDate weekDay endTime dateStartTimeStart dateEndTimeEnd Total time TimeW2-6 08-18 TimeW2-6 18-23 TimeW2-6 23-08 TimeW7 00-08 TimeW7 08-17....
09-06-2017 friday 17:10:00 09-06-2017 friday 22:00 09-06-2017 17:10 09-06-2017 22:00 04:50 00:50 04:00 00:00 00:00 00:00
09-06-2017 friday 21:59:00 11-06-2017 sunday 16:31 09-06-2017 21:59 11-06-2017 16:31 42:32 00:00 01:01 00:00 08:00 09:00

All suggestions are welcome

[sandy666]

any example excel file is welcome



To attach an Excel file to your post,

• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[sterling_archer]

Hi,

I think I managed to attach an example file.

Thanks

[davsth]

It is helpful for you to put in the example what your desired output is , and to cover all permutations.
What is the maximum duration? can the duration be over 24hrs?
from the file everything is dealt with on the same day, so presumably this is always the case, likewise nothing happens at a weekend, or is the example file insufficent?
likewise 23-08 weekday what does it mean if the start time is on a friday, but the finish time is a saturday?

the example file needs to but fit for purpose
p2 could be =IF(AND(WEEKDAY($E2,2)<6,$G2<0.75,$J2>=8/24),MIN($J2,0.75)-MAX($G2,8/24),"")
q2 could be =IF(AND(WEEKDAY($E2,2)<6,$G2<23/24,$J2>=18/24),MIN($J2,23/24)-MAX($G2,18/24),"")

but I expect you need to be more explicit

[sterling_archer]

Thanks for that!

The duration can be unlimited and can carry over between weekdays/weekends/times.

I added an example in the file attached. The highlighted area I calculated manually is ideally the output I am searching for.

Appologies if the formulas contain viking lingo.

[daddylonglegs]

For the Monday - Friday time periods you can use this formula to get hours between 8:00 and 18:00

=(NETWORKDAYS($E2,$H2)-1)*("18:00"-"8:00")+IF(NETWORKDAYS($H2,$H2),MEDIAN($J2,"8:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS($E2,$E2)*$G2,"8:00","18:00")

For Saturdays and Sundays I used a variation on these with NETWORKDAYS.INTL function because that enables you to specify just Saturday as a working day, e.g. for Saturday 08:00 - 17:00

=(NETWORKDAYS.INTL($E2,$H2,"1111101")-1)*("17:00"-"8:00")+IF(NETWORKDAYS.INTL($H2,$H2,"1111101"),MEDIAN($J2,"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL($E2,$E2,"1111101")*$G2,"8:00","17:00")

For other time period/days you can use variations on those, see attached where my formulas are in the "beige"(?) area

All my formula results agree with yours except for your results in N6 and P6, don't those need to swap?

Overnight is a little trickier to do so for 23:00 - 08:00 I just summed all weekday hours and subtracted the previous columns, i.e.

=NETWORKDAYS($E2,$H2)-1+IF(NETWORKDAYS($H2,$H2),$J2,"24:00")-NETWORKDAYS($E2,$E2)*$G2-SUM(N10:O10)

[sterling_archer]

@Daddlonglegs Wow! That is some mighty fine excel wizadry. It does the trick .

N6/P6 were placed incorrectly.

Awesome insights from all of you, thanks.