Counting hours

[vanilla_bean_orange via OfficeKB.com]

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

[Bob Phillips]

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

[vanilla_bean_orange via OfficeKB.com]

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

[Bob Phillips]

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

[vanilla_bean_orange via OfficeKB.com]

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

[vanilla_bean_orange via OfficeKB.com]

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]

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

[vanilla_bean_orange via OfficeKB.com]

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

[daddylonglegs]

Not sure what my original suggestion was ( ) but this should work 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 think you were OK with that.

I assume you're not expecting any negative time periods so you shouldn't need to use 1904 date system

[vanilla_bean_orange via OfficeKB.com]

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