Formatting Numarical Values with Custom Formatting

Hello:
I am attempting to solve a problem with either formatting or the equation itself to display a numerical value.

In cells "D" & "E" I have a custom format to display a time value in military time without entering in the the colon >>>formatting 00\:00<<<. This solved the problem of entering in a colon each time I needed to enter in a time. i.e if 1330 is entered in the cell the formatting converts to 13:30.
What I needed to do secondly is calculate the time difference between cells "D" & "E". So 13:30 in cell "D" and 1400 in cell "E". The difference is 30mim as time however its 70 when excel calculates the difference due to excel reading 13:30 and 14:00 as 1400 - 1330 which = 70. OK so I understand that issue and found away around it.
The formula I found works to calculate what I'm needing and looks like this >> =TEXT(E4,"00\:00")-TEXT(D4,"00\:00")+(E4>D4) <<. This equation functions to work the math to the correct outcome i.e 14:00 - 13:30 = 30. The formatting in the calculation cell is set to custom hh:mm which displays the math as 00:30.
(I hope that explained what I've done and where I'm at now)

What I need to know how to do is either a calculation modification to convert 00:30 to read 30 for 30min or 120 for 2hrs and so on such as 2hrs 45min reads 165. Or is custom formatting or even conditional formatting that could be used to display the time the way I'm needing?

Also I tried to use the equation and /24 to see if that worked and tried every formatting option excel had to offer

Try this:

=(TEXT(E4,"00\:00")-TEXT(D4,"00\:00")+(E4>D4))*24*60

Format the cell as General.

Hope this helps.

Pete

I entered in the alteration to the equation >> =(TEXT(E4,"00\:00")-TEXT(D4,"00\:00")+(E4>D4))*24*60 << Changed formatting to general and entered in my times and it added up to 1445, when the difference was just 35.
Did I enter in something wrong??
Would I need a macro to do the calculation after the fact i.e I let the original equation do its math and formatting be entered as is, then the macro takes over after data has been entered in the cell and formulates the 00:00 to the number value I'm after?

Oh man..
Just use -40 at end of your first formula..


Say thanks, click *

Use an extra code...
=lookup(difference,{0;100;200;300;400},{40;80;120;160;200})

just minus the ans from the differential....

You can extend this array to the maximum possibility of 2400..

I entered in your formula and formatted the cell to h:mm. When I entered in the times to formula gave me this #NAME?

Originally Posted by martindwilson

who are you talking to? you havent answered my question post #6
did you try the suggestion in post #7
try this
=MOD((TEXT(E4,"00\:00")-TEXT(D4,"00\:00")),1)*24*60

Sorry about that; I was trying to respond to your answer only hit the wrong reply.
I have tried both of your suggestions and I'm unable to come up with the results I'm looking for I have to be entering something wrong.

Under general formatting =MOD((TEXT(E4,"00\:00")-TEXT(D4,"00\:00")),1)*24*60<< is giving me a "0".
I'll figure this thing out one way or another. Thank you for your help.

Originally Posted by Pete_UK

Try this:

=(TEXT(E4,"00\:00")-TEXT(D4,"00\:00")+(E4>D4))*24*60

Format the cell as General.

Hope this helps.

Pete

This got me to where I needed relating to the getting the math to come out right. However I'm having problems getting it to show up as only numbers i.e 2hrs 5min looks like 02:05 instead of 125.

Originally Posted by excelneub

Sorry about that; I was trying to respond to your answer only hit the wrong reply.
I have tried both of your suggestions and I'm unable to come up with the results I'm looking for I have to be entering something wrong.

Under general formatting =MOD((TEXT(E4,"00\:00")-TEXT(D4,"00\:00")),1)*24*60<< is giving me a "0".
I'll figure this thing out one way or another. Thank you for your help.

Thank You!!!! Finally figured out where i was making my error form this has solved my problem. Thank you so much!!