Subtracting 24hr clocks?

[devoted_darling]

HI, HELP!

I'm trying to generate the formula to subtract 24hr time clocks. If I want
to work out the hours between 0500 and 1600 then it's a simple case of
subtraction - however, the problem is when I try and reverse this. I'm trying
to calculate a formula to work out the hours between, for example, 1600 and
0630 - the formula I've tried using doesn't work so is obviously wrong.

Please help!

 

[Stefi]

start time:A9
end time:B9

=B9+IF(A9>B9,1,0)-A9

Regards,
Stefi

„devoted_darling†ezt írta:

 

[David Biddulph]

=MOD(B1-A1,1)

 

[devoted_darling]

Hi Stefi,

This doesn't seem to work?!

I'm trying to work out the hours between 1600 (start) and 0800 (end) but
it's coming out with a minus figure.

Is there any way I can do this?

Thanks

Clare

 

[devoted_darling]

Hi David,

Thankyou for your reply, however the formula brings back 0 when it whould
bring back a number as it's calculating the hours worked between the 2 hours
posted.....

Any ideas?

Clare

 

[David Biddulph]

I wonder whether you entered your times correctly? Remember that Excel
times are entered as 16:00 and 06:30, not 1600 and 0630.

 

[devoted_darling]

Hi Stefi, yes that's correct. They are currently in text format - do they
need to be changed to time format for this to work?

Clare

 

[devoted_darling]

Hi David,

yes the times have been entered incorrectly - unfortunately I'm working from
someone elses data so they have been entered as 1600 instead of 16:00. I'll
change them and try again!

Thanks

Clare

 

[Stefi]

Yes, convert them with this function:
=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2))
and format the result cells like "hh:mm"!

Use David's formula because it's nicer!

Regards,
Stefi

„devoted_darling†ezt írta:

 

[David Biddulph]

To convert your incorrectly entered times, use =--TEXT(A1,"00\:00") and
format as time appropriately.

 

[devoted_darling]

David,

I've changed the format to 16:00 etc and it's working brilliantly now!

Thanku

Clare