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)
Last edited by TechRetard; 04-18-2010 at 02:18 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)
Please Login or Register to view this content.
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.
Last edited by DonkeyOte; 04-18-2010 at 02:42 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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
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")
Entia non sunt multiplicanda sine necessitate
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")
Actually, vice versa -- mine was a variation of yoursAnother variation of shg's approach might be:
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!
I recommend DO's -- it's more compact and easier to understand.
Donkey that seems to work as well
nice job guys, i've been slaving over this for hours...much appreciated
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
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks