How to subtract military time i.e. 1503-1455 or 3:03pm-2:55pm?

[Guest]

In Excel my data has military time and I need to subtract the time to get
elapsed minutes. When I subract 1503-1455 (3:03pm - 2:55pm) I get 48
minutes. I should only be 8 minutes. How can I get Excel to recognize the
data as time.

 

[Niek Otten]

Best would be to input your times as 15:03 and 14:55; than you could simply subtract the smaller one from the other.
If that can't be done you can convert to time with this formula:
=TIME(INT(A1/100),MOD(A1,100),0)
Do this for both cells and subtract.

--
Kind regards,

Niek Otten

| In Excel my data has military time and I need to subtract the time to get
| elapsed minutes. When I subract 1503-1455 (3:03pm - 2:55pm) I get 48
| minutes. I should only be 8 minutes. How can I get Excel to recognize the
| data as time.

 

[Niek Otten]

Format the result Custom as [h]:mm

--
Kind regards,

Niek Otten

| Best would be to input your times as 15:03 and 14:55; than you could simply subtract the smaller one from the other.
| If that can't be done you can convert to time with this formula:
| =TIME(INT(A1/100),MOD(A1,100),0)
| Do this for both cells and subtract.
|
| --
| Kind regards,
|
| Niek Otten
|
|| In Excel my data has military time and I need to subtract the time to get
|| elapsed minutes. When I subract 1503-1455 (3:03pm - 2:55pm) I get 48
|| minutes. I should only be 8 minutes. How can I get Excel to recognize the
|| data as time.
|
|

 

[Guest]

Thank you. It works. One final question though. Now that I have the 0:08
minutes that I was trying to calculate, is there a way to convert 0:08 to a
real number "8" so that I can use it in another calculation?

 

[daddylonglegs]

Thank you. It works. One final question though. Now that I have the
0:08
minutes that I was trying to calculate, is there a way to convert 0:08
to a
real number "8" so that I can use it in another calculation?


simply subtract the smaller one from the other.

To convert a time to the decimal number of minutes multiply by 1440. So
if you are following Niek's suggestion to input times as hh:mm and
subtracting

=(B1-A1)*1440

although if there is a possibility that your time period may cross
midnight

=MOD(B1-A1,1)*1440

 

[Guest]

Can someone explain to me how this works?
I used it, and it works, yet I don't understand why it works. It might help
me figure out some other things.
Thanks.

 

[David Biddulph]

"daddylonglegs" wrote:

Can someone explain to me how this works?
I used it, and it works, yet I don't understand why it works. It might
help
me figure out some other things.

Excel times are in days.
Multiplying by 24 gets it into hours, and another 60 gets it into minutes.

 

[Guest]

I tried the suggestion below, but it is not calculating when time crosses
over midnight. How do you calculate the time difference from 2330 hrs to 0100
hrs?

 

[Roger Govier]

Hi

If you don't multiply by 1440, the time displayed will be 1:30.
If you multiply by 1440 to convert to minutes, then the result is decimal.
In order to show that (as opposed to seeing 00:00) you need to format the
cell with the formula.
Format>Cells>Number>General
then you will see the result as 90

 

[Guest]

Thanks Roger,

I will try it out later as am kinda busy right now with my month end reports.