How about this
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(B29)&":"&INT(D29))))={2, 3,4,5,6}))-2
+(MAX(MOD(B29,1),F3)-MAX(MOD(B29,1),E3))*(WEEKDAY(B29,2)<6)
+(MIN(MOD(D29,1),F3)-MIN(MOD(D29,1),E3))*(WEEKDAY(D29,2)<6)
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"vanilla_bean_orange via OfficeKB.com" <u16604@uwe wrote in message
news:5cfecdc01392d@uwe...
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