software

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.

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

quot;jetmendozaquot; gt; wrote in message ...
| 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.
Format the result Custom as [h]:mm

--
Kind regards,

Niek Otten

quot;Niek Ottenquot; gt; wrote in message ...
| 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
|
| quot;jetmendozaquot; gt; wrote in message ...
|| 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.
|
|
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 quot;8quot; so that I can use it in another calculation?

quot;Niek Ottenquot; wrote:

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


jetmendoza Wrote:
gt; Thank you. It works. One final question though. Now that I have the
gt; 0:08
gt; minutes that I was trying to calculate, is there a way to convert 0:08
gt; to a
gt; real number quot;8quot; so that I can use it in another calculation?
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt; gt; Best would be to input your times as 15:03 and 14:55; than you could
gt; simply subtract the smaller one from the other.
gt; gt; If that can't be done you can convert to time with this formula:
gt; gt; =TIME(INT(A1/100),MOD(A1,100),0)
gt; gt; Do this for both cells and subtract.
gt; gt;
gt; gt; --
gt; gt; Kind regards,
gt; gt;
gt; gt; Niek Otten
gt; gt;
gt; gt; quot;jetmendozaquot; gt; wrote in message
gt; ...
gt; gt; | In Excel my data has military time and I need to subtract the time
gt; to get
gt; gt; | elapsed minutes. When I subract 1503-1455 (3:03pm - 2:55pm) I get
gt; 48
gt; gt; | minutes. I should only be 8 minutes. How can I get Excel to
gt; recognize the
gt; gt; | data as time.
gt; gt;
gt; gt;
gt; gt;

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--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=532949Can 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.
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.quot;daddylonglegsquot; wrote:

gt;
gt; jetmendoza Wrote:
gt; gt; Thank you. It works. One final question though. Now that I have the
gt; gt; 0:08
gt; gt; minutes that I was trying to calculate, is there a way to convert 0:08
gt; gt; to a
gt; gt; real number quot;8quot; so that I can use it in another calculation?
gt; gt;
gt; gt; quot;Niek Ottenquot; wrote:
gt; gt;
gt; gt; gt; Best would be to input your times as 15:03 and 14:55; than you could
gt; gt; simply subtract the smaller one from the other.
gt; gt; gt; If that can't be done you can convert to time with this formula:
gt; gt; gt; =TIME(INT(A1/100),MOD(A1,100),0)
gt; gt; gt; Do this for both cells and subtract.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Kind regards,
gt; gt; gt;
gt; gt; gt; Niek Otten
gt; gt; gt;
gt; gt; gt; quot;jetmendozaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; | In Excel my data has military time and I need to subtract the time
gt; gt; to get
gt; gt; gt; | elapsed minutes. When I subract 1503-1455 (3:03pm - 2:55pm) I get
gt; gt; 48
gt; gt; gt; | minutes. I should only be 8 minutes. How can I get Excel to
gt; gt; recognize the
gt; gt; gt; | data as time.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt; To convert a time to the decimal number of minutes multiply by 1440. So
gt; if you are following Niek's suggestion to input times as hh:mm and
gt; subtracting
gt;
gt; =(B1-A1)*1440
gt;
gt; although if there is a possibility that your time period may cross
gt; midnight
gt;
gt; =MOD(B1-A1,1)*1440
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=532949
gt;
gt;

quot;Hanr3quot; gt; wrote in message
...

gt; quot;daddylonglegsquot; wrote:

gt;gt; To convert a time to the decimal number of minutes multiply by 1440. So
gt;gt; if you are following Niek's suggestion to input times as hh:mm and
gt;gt; subtracting
gt;gt;
gt;gt; =(B1-A1)*1440
gt;gt;
gt;gt; although if there is a possibility that your time period may cross
gt;gt; midnight
gt;gt;
gt;gt; =MOD(B1-A1,1)*1440

gt; Can someone explain to me how this works?
gt; I used it, and it works, yet I don't understand why it works. It might
gt; help
gt; 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.
--
David Biddulph
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?

quot;David Biddulphquot; wrote:

gt; quot;Hanr3quot; gt; wrote in message
gt; ...
gt;
gt; gt; quot;daddylonglegsquot; wrote:
gt;
gt; gt;gt; To convert a time to the decimal number of minutes multiply by 1440. So
gt; gt;gt; if you are following Niek's suggestion to input times as hh:mm and
gt; gt;gt; subtracting
gt; gt;gt;
gt; gt;gt; =(B1-A1)*1440
gt; gt;gt;
gt; gt;gt; although if there is a possibility that your time period may cross
gt; gt;gt; midnight
gt; gt;gt;
gt; gt;gt; =MOD(B1-A1,1)*1440
gt;
gt; gt; Can someone explain to me how this works?
gt; gt; I used it, and it works, yet I don't understand why it works. It might
gt; gt; help
gt; gt; me figure out some other things.
gt;
gt; Excel times are in days.
gt; Multiplying by 24 gets it into hours, and another 60 gets it into minutes.
gt; --
gt; David Biddulph
gt;
gt;
gt;

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.
Formatgt;Cellsgt;Numbergt;General
then you will see the result as 90

--
Regards
Roger Govier
quot;almitomquot; gt; wrote in message
...
gt;I tried the suggestion below, but it is not calculating when time crosses
gt; over midnight. How do you calculate the time difference from 2330 hrs to
gt; 0100
gt; hrs?
gt;
gt; quot;David Biddulphquot; wrote:
gt;
gt;gt; quot;Hanr3quot; gt; wrote in message
gt;gt; ...
gt;gt;
gt;gt; gt; quot;daddylonglegsquot; wrote:
gt;gt;
gt;gt; gt;gt; To convert a time to the decimal number of minutes multiply by 1440.
gt;gt; gt;gt; So
gt;gt; gt;gt; if you are following Niek's suggestion to input times as hh:mm and
gt;gt; gt;gt; subtracting
gt;gt; gt;gt;
gt;gt; gt;gt; =(B1-A1)*1440
gt;gt; gt;gt;
gt;gt; gt;gt; although if there is a possibility that your time period may cross
gt;gt; gt;gt; midnight
gt;gt; gt;gt;
gt;gt; gt;gt; =MOD(B1-A1,1)*1440
gt;gt;
gt;gt; gt; Can someone explain to me how this works?
gt;gt; gt; I used it, and it works, yet I don't understand why it works. It might
gt;gt; gt; help
gt;gt; gt; me figure out some other things.
gt;gt;
gt;gt; Excel times are in days.
gt;gt; Multiplying by 24 gets it into hours, and another 60 gets it into
gt;gt; minutes.
gt;gt; --
gt;gt; David Biddulph
gt;gt;
gt;gt;
gt;gt;
Thanks Roger,

I will try it out later as am kinda busy right now with my month end reports.
quot;Roger Govierquot; wrote:

gt; Hi
gt;
gt; If you don't multiply by 1440, the time displayed will be 1:30.
gt; If you multiply by 1440 to convert to minutes, then the result is decimal.
gt; In order to show that (as opposed to seeing 00:00) you need to format the
gt; cell with the formula.
gt; Formatgt;Cellsgt;Numbergt;General
gt; then you will see the result as 90
gt;
gt; --
gt; Regards
gt; Roger Govier
gt;
gt;
gt;
gt; quot;almitomquot; gt; wrote in message
gt; ...
gt; gt;I tried the suggestion below, but it is not calculating when time crosses
gt; gt; over midnight. How do you calculate the time difference from 2330 hrs to
gt; gt; 0100
gt; gt; hrs?
gt; gt;
gt; gt; quot;David Biddulphquot; wrote:
gt; gt;
gt; gt;gt; quot;Hanr3quot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt;
gt; gt;gt; gt; quot;daddylonglegsquot; wrote:
gt; gt;gt;
gt; gt;gt; gt;gt; To convert a time to the decimal number of minutes multiply by 1440.
gt; gt;gt; gt;gt; So
gt; gt;gt; gt;gt; if you are following Niek's suggestion to input times as hh:mm and
gt; gt;gt; gt;gt; subtracting
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =(B1-A1)*1440
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; although if there is a possibility that your time period may cross
gt; gt;gt; gt;gt; midnight
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; =MOD(B1-A1,1)*1440
gt; gt;gt;
gt; gt;gt; gt; Can someone explain to me how this works?
gt; gt;gt; gt; I used it, and it works, yet I don't understand why it works. It might
gt; gt;gt; gt; help
gt; gt;gt; gt; me figure out some other things.
gt; gt;gt;
gt; gt;gt; Excel times are in days.
gt; gt;gt; Multiplying by 24 gets it into hours, and another 60 gets it into
gt; gt;gt; minutes.
gt; gt;gt; --
gt; gt;gt; David Biddulph
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;