subtracting 1 time from another.

[aburkho]

Hey Guys,


I am having trouble subtracting one time from another in Excel in order to get the difference of time. How do i subtract 12:00:00 AM from 5:35 AM to get an answer of 5:35 in the first example? I tried to just do (=e3-d3), but it came up "#Value"

Would love to figure this one out.

[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]12:00am
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]5:35am
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10:05am
[/TD]
[TD="class: xl67, bgcolor: transparent"]10:10am
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]#VALUE!
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]10:00am
[/TD]
[TD="class: xl67, bgcolor: transparent"]10:05am
[/TD]
[TD="class: xl65, bgcolor: transparent, align: center"]#VALUE!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

You must enter your times like this:
8:00 AM or
3:00 PM
Note the space
Assuming your using U.S time formats.

Or if you like you can enter your times like this:

3:00 Which is 3 AM
Or:
17:00 Which is 5 PM

 

[lewisbarton]

Hi :0)

Lets call first time 12:00am cell A1 and you want to subtract cell B1 "5:35".
Get rid of the am/pm format and enter it as a 24 hour clock

Formula for this is =(b1+IF(a1>b1,1,0)-A1)*24) That should do it if you want how many hours are inbetween. if you want it to an actual time you will have to format the cells as time an date and enter the date manually.

Regards
Lewis Barton