Hello-
I have a question on how to calculate the # of business hours between to time date fields, we staff on weekends so I don't want to eliminate them. I was using NetWorkDays until we added the weekend hours and am not sure of a work around.
Cell A1 = 2/20/2010 2:30:00 PM
Cell B1 = 2/21/2010 2:45:00 PM
Cell C1 = 9:15:00 [HH:MM:SS]
Weekend business hours are 7:30AM to 4:30PM
Any suggestions would be appreciated.
Thanks
ClikClak
Assuming the hours are the same for all 7 days perhaps:
C1: =($E$2-$E$1)*INT(B1-A1)+(MEDIAN(MOD(B1,1),$E$1:$E$2)-MEDIAN(MOD(A1,1),$E$1:$E$2))
format as [hh]:mm
where E2 is end shift (16:30) and E1 is start shift (07:30)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
That worked, thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks