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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Hi again!!
Thanks for getting back Tried but it comes up with repeated ########. Ive checked its not because the cells too small. Any ideas? Theresa Bob Phillips wrote: 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) 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) [quoted text clipped - 12 lines] Theresa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Hi Theresa,
I have just tried it again, and it woks as I understand it. What do you have in B29, D29, E3 and F3? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "vanilla_bean_orange via OfficeKB.com" <u16604@uwe wrote in message news:5d0d6d3964732@uwe... Hi again!! Thanks for getting back Tried but it comes up with repeated ########. Ive checked its not because the cells too small. Any ideas? Theresa Bob Phillips wrote: 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) 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) [quoted text clipped - 12 lines] Theresa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Hi Thanks for the reply and sorry for the delay
B29 has the start time as date and time e.g. 01/01/2000 10:00 D29 has the end time in the same format E3 has 10:00 (when hours can start being counted) F3 has 16:00 (when hours must stop being counted) Bob Phillips wrote: Hi Theresa, I have just tried it again, and it woks as I understand it. What do you have in B29, D29, E3 and F3? Hi again!! [quoted text clipped - 31 lines] Theresa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Hey
The problem seems to be with the format. When it is in general format I can get a result. When I format into my custom dd"days" hh"hours" mm"minutes" it returns the ######### I know im being dumb but why would this be!!? T vanilla_bean_orange wrote: Hi again!! Thanks for getting back Tried but it comes up with repeated ########. Ive checked its not because the cells too small. Any ideas? Theresa How about this [quoted text clipped - 20 lines] Theresa -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Is the column wide enough? Try widening it way past what you think is
necessary. Is the value negative? Excel doesn't like negative dates/times unless you turn on: tools|Options|calculation tab|1904 date system (Then watch your dates--they could be off by 4 years and 1 day.) "vanilla_bean_orange via OfficeKB.com" wrote: Hey The problem seems to be with the format. When it is in general format I can get a result. When I format into my custom dd"days" hh"hours" mm"minutes" it returns the ######### I know im being dumb but why would this be!!? T vanilla_bean_orange wrote: Hi again!! Thanks for getting back Tried but it comes up with repeated ########. Ive checked its not because the cells too small. Any ideas? Theresa How about this [quoted text clipped - 20 lines] Theresa -- Message posted via http://www.officekb.com -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Hi there, thanks for the reply
Changing to the 1904 date systems does solve the format problem. I think I can get around the date change problem. Theres not to many dates in there yet thankfully. Would you know anything about the formula? It doesnt seem to be returning the right value. E.g: B29 - 01/03/2006 10:00 D29 - 01/03/2006 17:00 E3 - 10:00 F3 - 16:00 The value that should be returned is 6 hours as anytime before 10am shouldnt be counted as with anytime after 16:00. Instead I get -1day 12hours 0minutes! Dave Peterson wrote: Is the column wide enough? Try widening it way past what you think is necessary. Is the value negative? Excel doesn't like negative dates/times unless you turn on: tools|Options|calculation tab|1904 date system (Then watch your dates--they could be off by 4 years and 1 day.) Hey [quoted text clipped - 22 lines] Theresa -- Message posted via http://www.officekb.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Not sure what my original suggestion was ( ) but this should wor for you =IF(B29*D29,(INT(D29)-INT(B29)-INT((WEEKDAY(B29-1)+INT(D29)-INT(B29))/7))*($F$3-$E$3)+IF(WEEKDAY(D29)=1,$F$3,MEDIAN(MOD(D29,1),$F$ 3,$E$3))-IF(WEEKDAY(B29)=1,$E$3,MEDIAN(MOD(B29,1),$F$3,$E$3 )),"") Note: as I've said before the custom format dd"days" hh"hours" mm"minutes" won't show any time period above 31days 23hours 59minutes but I thin you were OK with that. I assume you're not expecting any negative time periods so yo shouldn't need to use 1904 date syste -- daddylongleg ----------------------------------------------------------------------- daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048 View this thread: http://www.excelforum.com/showthread.php?threadid=52052 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting hours
Works great hurrah!
Thanks so much Forever in your debt T daddylonglegs wrote: Not sure what my original suggestion was ( ) but this should wor for you =IF(B29*D29,(INT(D29)-INT(B29)-INT((WEEKDAY(B29-1)+INT(D29)-INT(B29))/7))*($F$3-$E$3)+IF(WEEKDAY(D29)=1,$F$3,MEDIAN(MOD(D29,1),$F$ 3,$E$3))-IF(WEEKDAY(B29)=1,$E$3,MEDIAN(MOD(B29,1),$F$3,$E$3 )),"") Note: as I've said before the custom format dd"days" hh"hours" mm"minutes" won't show any time period above 31days 23hours 59minutes but I thin you were OK with that. I assume you're not expecting any negative time periods so yo shouldn't need to use 1904 date syste -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200603/1 |
Reply |
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) |