finding # hrs between 2 date times

**TechRetard** · 04-17-2010, 06:44 PM

Excel 2007

Situation: I have two separate dates in format MM/DD/YYYY HH:MM...I want to find the number of hours and minutes between the two date times, but I only want it to calculate between 8am and 8pm.

example:

date 1 = 4/16/2010 16:00
date 2 = 4/17/2010 22:00

answer = 16 hours 0 minutes (16.0 is fine)

**TechRetard** · 04-17-2010, 10:15 PM

Ok, here is what I got...seems to be working however I don't know if it is the most efficient

Variables needed:
StartDT = MM/DD/YY HH:MM (ex 4/15/10 8:00)
EndDT = MM/DD/YY HH:MM (ex 4/17/10 20:00)

StartTheDay = HH:MM (ex 8:00)
EndTheDay = HH:MM (ex 20:00)

StartDate = INT(StartDT) (ex 40283)
EndDate = INT(EndDT) (ex 40285)

ModStartDT = StartDT-1*INT(StartDT/1) (ex .33333)
ModEndDT = =EndTime-1*INT(EndTime/1) (ex .833333)

Solution

(answer = 36 for above example)

=(DAYS360(StartDate, EndDate) - 1) * (24 * (EndTheDay - StartTheDay)) + IF((24*(ModStartDT-StartTheDay))>0,IF((24*(EndTheDay-ModStartDT))<0,0,(24*(EndTheDay-ModStartDT))),12) + IF((24*(ModEndDT-StartTheDay))<0,0,IF((24*(ModEndDT-StartTheDay))>12,12,(24*(ModEndDT-StartTheDay))))

**DonkeyOte** · 04-18-2010, 02:39 AM

Can you clarify that this is independent of weekday - ie 0800-2000 7 days a week ?

Based on your examples:

=SUM(INT(A2-A1)*("20:00"-"08:00"),MEDIAN(MOD(A2,1),"20:00","08:00")-MEDIAN(MOD(A1,1),"20:00","08:00"))

Where A2 is end date time and A1 start date time.

**TechRetard** · 04-18-2010, 11:47 AM

It is 7 days a week, however the start and end datetime may be outside the range of 8am to 8pm, and can be over multiple days...

I could only get your formula to work within 1 day and within 8am to 8pm - but when i inputed 4/16/2010 08:00 to 4/18/2010 07:00 it returned "12:00" instead of 24

**shg** · 04-18-2010, 01:22 PM

I believe this works, and expect it can be simplified:

=(INT(A2) - CEILING(A1,1) + 1) * ("20:00"-"08:00") - MEDIAN(MOD(A1,1), "20:00", "8:00") + MEDIAN(MOD(A2,1), "20:00", "8:00")

**DonkeyOte** · 04-18-2010, 01:32 PM

Apologies on my part - I should have tested

Another variation of shg's approach might be:

=((INT(A2)-INT(A1))*("20:00"-"08:00"))-MEDIAN(MOD(A1,1),"20:00","8:00")+MEDIAN(MOD(A2,1),"20:00","8:00")

**TechRetard** · 04-18-2010, 01:37 PM

nice sgh.. i think that will do it

i'll keep testing and let you know if I encounter any weird numbers but so far so good

rock on!

**royUK** · 04-18-2010, 02:04 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save