Help with time duration formula..

[amper]

Hi,

i'm new to timesheets, but had a question regarding modifying the formula listed below:

=Q2-P2+(Q2<A2)

it works as intended for a time interval that crosses over midnight, but with any normal interval, an additional 24 hours is added to my duration.

i'm attempting to use a single formula for all durations, so is there anyway to integrate an IF/THEN statement to subtract 24:00 hours if the calculated value is greater than 24:00?

[VBA Noob]

Hi,

Would this help

=IF(Q2<P2,P2-Q2,Q2-P2)

Don't think you need A2 ??

VBA Noob

[Bryan Hessey]

Hi,

i'm new to timesheets, but had a question regarding modifying the formula listed below:

=Q2-P2+(Q2<A2)

it works as intended for a time interval that crosses over midnight, but with any normal interval, an additional 24 hours is added to my duration.

i'm attempting to use a single formula for all durations, so is there anyway to integrate an IF/THEN statement to subtract 24:00 hours if the calculated value is greater than 24:00?

Hi,

another option is to use

=Mod( calculatedvalue ,1)

hth
---

[amper]

EDIT~

=Q2-P2+(Q2<A2)

where:
A2 = some date, say 12/10/2006
P2 = 23:50
Q2 = 01:10

value would be 01:10, whereas using 01:00 and 02:00 would result in 25:00 instead of 01:00 that i'm aiming for. i'd still like to use a single formula for each duration cell (i'm putting together a form for mass usage). also, i'm not familiar with combining several arguments into one formula (apologies :x)

[Bryan Hessey]

EDIT~

=Q2-P2+(Q2<A2)

where:
A2 = some date, say 12/10/2006
P2 = 23:50
Q2 = 01:10

value would be 01:10, whereas using 01:00 and 02:00 would result in 25:00 instead of 01:00 that i'm aiming for. i'd still like to use a single formula for each duration cell (i'm putting together a form for mass usage). also, i'm not familiar with combining several arguments into one formula (apologies :x)

=Mod( your calculation ,1)

for you

=Mod((Q2-P2+(Q2<A2)),1)

----

[daddylonglegs]

EDIT~

=Q2-P2+(Q2<A2)

where:
A2 = some date, say 12/10/2006
P2 = 23:50
Q2 = 01:10

You're using the wrong formula

Assuming Q2 just contains a time and A2 a date then Q2 will always be less than A2 so your formula is the same as

=Q2-P2+1, which, as you've discovered, will give you the wrong result whenever the shift doesn't cross midnight.

You should use

=Q2-P2+(P2>Q2)

or

=MOD(Q2-P2,1)

[amper]

thanks very much.. that was much cleaner than all the round about ways i thought up. cheers