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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks