Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
A very kind daddylonglegs and bob phillips assisted me in giving me the below
formula, it basically counts hours excluding sundays and hours stated in E3 and F3 =IF(ISERROR(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&ROUND((D29-B29)*1440,0))) /1440+B29-1/2880)<1),--(ABS(MOD(ROW(INDIRECT("1:"&ROUND((D29-B29)*1440,0))) /1440+B29-1/2880,1)-(E3+F3)/2)(F3-E3)/2)))/1440," 00 days 00 hours 00 minutes",(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("1:"&ROUND((D29-B29)*1440,0))) /1440+B29-1/2880)<1),--(ABS(MOD(ROW(INDIRECT("1:"&ROUND((D29-B29)*1440,0))) /1440+B29-1/2880,1)-(E3+F3)/2)(F3-E3)/2)))/1440) D29 Being end date and time (user input) B29 being start date and time (user input) F3 being core hour end time (set time) E3 being core hour start time (set time) Can anyone tell me how to adjust this formula so that hours counted are only the hours between E3 to F3. Instead of excluding hours between E3 to F3 id like to exclude all hours outside E3 to F3. I dont have NETWORKDAYS unfortunately which would no doubt make things easier. Any ideas once again would be greatly appreciated. Theresa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting hours | Excel Discussion (Misc queries) | |||
Counting DD:HH:MM NETWORKDAYS and Hours | Excel Worksheet Functions | |||
Date (hours and still counting) | Excel Worksheet Functions | |||
Counting accummilation of hours?? | Excel Programming | |||
Counting Occurences of Hours of the Day | Excel Discussion (Misc queries) |