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

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

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

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

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

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

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

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

Counting hours
 

Not sure what my original suggestion was ( :mad: ) 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

Counting hours
 

Works great hurrah!

Thanks so much

Forever in your debt

T

daddylonglegs wrote:
Not sure what my original suggestion was ( :mad: ) 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