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

 

[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)

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))
)

 

[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

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)-(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)-(E3+F3)/2)>(F3-E3)/2)))/1440)

[quoted text clipped - 12 lines]

Theresa

 

[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)

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

=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

 

[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)

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]

 

[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

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]

 

[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.)

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

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

 

[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!

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]

 

[daddylonglegs]

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

 

[vanilla_bean_orange via OfficeKB.com]

Works great hurrah!

Thanks so much

Forever in your debt

T