Subtract times (dates)

  • Thread starter Thread starter Garry Kennedy
  • Start date Start date
G

Garry Kennedy

Hi,

I have been asked to put a timesheet togther for wafes caluulation in a
small business. There are night shifts involved and whilst i can get one
column to subtract from the adjacent column (start time / finish time) i
cannot make it do it when the start time is say 19:00 hours an a monday eve
and the finish time is 07.00 hours on the tuesday morning ....

anyone any ideas ?

Many thanks

Garry
 
Hi,

I have been asked to put a timesheet togther for wafes caluulation in a
small business. There are night shifts involved and whilst i can get one
column to subtract from the adjacent column (start time / finish time) i
cannot make it do it when the start time is say 19:00 hours an a monday eve
and the finish time is 07.00 hours on the tuesday morning ....

anyone any ideas ?

Many thanks

Garry
Click to expand...

=B1-A1+(B1<A1)

where A1: start time and B1: end time
--ron
 
Tried that Ron, maybe i have to do some formatting or something ... if i put
19:00 in a1 as start time and 07:00 in B1 as Finish time, the formula gives
me 0.5 ...
(I am probable doing something dim)
 
Just got it - i had the cell formatted as a number instead of time (hh:mm)
Many thanks Ron - so simple when you see it in retrospect ...

very greatful!

Garry
 
Just got it - i had the cell formatted as a number instead of time (hh:mm)
Many thanks Ron - so simple when you see it in retrospect ...

very greatful!

Garry
Click to expand...

Glad to help. Thanks for the feedback.
--ron
 
hey,

I just see you solve this problem, it inspire me. But I just have a question
about this problem too. If the begin time and the finish time are all 17:00
but not the same day. How to make it show 24hours not 0hours. :)

Any ideas, Ron?

Many thanks

Shermen
 
hey,

I just see you solve this problem, it inspire me. But I just have a question
about this problem too. If the begin time and the finish time are all 17:00
but not the same day. How to make it show 24hours not 0hours. :)

Any ideas, Ron?

Many thanks

Shermen
Click to expand...

If the difference will never be greater than 24 hours, then use:

=B1-A1+(B1<=A1)

If greater, then you will need to also enter dates.

Custom Format the cell: [h]:mm
--ron
 
Hey
it works! thanks a lot, Ron and Roger. But why is that different between the
format hh:ss and [h]:ss?

many thanks

shermen
 
Hey
it works! thanks a lot, Ron and Roger. But why is that different between the
format hh:ss and [h]:ss?

many thanks

shermen
Click to expand...

h displays hours as 0-23
[h] displays elapsed hours (e.g. it will display more than 23 hours)
--ron
 
Back
Top