Subtracting 24hr clocks?
 

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!

Subtracting 24hr clocks?
 

start time:A9
end time:B9

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

Regards,
Stefi

€ždevoted_darling€� ezt Ã*rta:

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!

Subtracting 24hr clocks?
 

=MOD(B1-A1,1)
--
David Biddulph

"devoted_darling" wrote in
message ...
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!

Subtracting 24hr clocks?
 

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

"Stefi" wrote:

start time:A9
end time:B9

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

Regards,
Stefi

€ždevoted_darling€� ezt Ã*rta:

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!

Subtracting 24hr clocks?
 

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" wrote:

=MOD(B1-A1,1)
--
David Biddulph

"devoted_darling" wrote in
message ...
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!

Subtracting 24hr clocks?
 

I have a guess: 1600 (start) and 0800 (end) suggests that your data are not
of real Excel time format but text strings, otherwise they would look like
16:00 (start) and 08:00 (end). Am I right?

Stefi


€ždevoted_darling€� ezt Ã*rta:

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

"Stefi" wrote:

start time:A9
end time:B9

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

Regards,
Stefi

€ždevoted_darling€� ezt Ã*rta:

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!

Subtracting 24hr clocks?
 

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

"devoted_darling" wrote in
message ...
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" wrote:

=MOD(B1-A1,1)
--
David Biddulph

"devoted_darling" wrote in
message ...
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!

Subtracting 24hr clocks?
 

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

"Stefi" wrote:

I have a guess: 1600 (start) and 0800 (end) suggests that your data are not
of real Excel time format but text strings, otherwise they would look like
16:00 (start) and 08:00 (end). Am I right?

Stefi


€ždevoted_darling€� ezt Ã*rta:

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

"Stefi" wrote:

start time:A9
end time:B9

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

Regards,
Stefi

€ždevoted_darling€� ezt Ã*rta:

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!

Subtracting 24hr clocks?
 

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

"David Biddulph" wrote:

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

"devoted_darling" wrote in
message ...
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" wrote:

=MOD(B1-A1,1)
--
David Biddulph

"devoted_darling" wrote in
message ...
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!

Subtracting 24hr clocks?
 

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:

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

"Stefi" wrote:

I have a guess: 1600 (start) and 0800 (end) suggests that your data are not
of real Excel time format but text strings, otherwise they would look like
16:00 (start) and 08:00 (end). Am I right?

Stefi


€ždevoted_darling€� ezt Ã*rta:

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

"Stefi" wrote:

start time:A9
end time:B9

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

Regards,
Stefi

€ždevoted_darling€� ezt Ã*rta:

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!

Subtracting 24hr clocks?
 

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


"devoted_darling" wrote in
message ...
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

"David Biddulph" wrote:

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

"devoted_darling" wrote in
message ...
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" wrote:

=MOD(B1-A1,1)
--
David Biddulph

"devoted_darling" wrote in
message ...
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!

Subtracting 24hr clocks?
 

David,

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

Thanku

Clare :)

"devoted_darling" wrote:

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

"David Biddulph" wrote:

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

"devoted_darling" wrote in
message ...
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" wrote:

=MOD(B1-A1,1)
--
David Biddulph

"devoted_darling" wrote in
message ...
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!