How to subtract times i.e. 18:55 day1 from 03:03 day2

J

Jaclyn

I am making a timecard for workshifts that span two days. There are no dates
entered into the worksheet. I want excel to find the hours and minutes from
the start of a shift (18:55 day1) to the end of a shift (3:03 day2).

Thanks,
Jackie
 
R

Ron Rosenfeld

I am making a timecard for workshifts that span two days. There are no dates
entered into the worksheet. I want excel to find the hours and minutes from
the start of a shift (18:55 day1) to the end of a shift (3:03 day2).

Thanks,
Jackie
Click to expand...

If your hours worked are always less than 24, then:

=EndTime-StartTime+(EndTime<StartTime)

will work.

If your hours worked might be 24 or greater, you will need to enter dates.


--ron
 
K

Ken Johnson

Hi Jaclyn,
If 18:55 day1 is in A1,
3:03 day2 is in B1, then =LEFT(B1,FIND(" ",B1)-1)-LEFT(A1,FIND("
",A1)-1) +1 in C1 yields 8:08 provided C1 has custom formatting [h]:mm.
Ken Johnson
 
R

Roger Govier

Hi Jaclyn

With start time in A1 and end time in B1
=MOD(B1-A1,1)
will deal with the times within the same day, or crossing two days.
 
Top