Military time Conversion

[PlumGr8]

So I have an excel sheet where I am trying to make it so we don't have to type the ":", meaning I can just type 0200 and have it show 02:00. Which I can do with custom formatting, issue is I also have a formula that is used to calculate the overall time between 2 entries. If I change it so that "0130" converts to "01:30", the time calculation no longer works correctly.

The formula currently for calculating time is " =IF(B287=0,"",(B287+E287)*24-(A287+D287)*24) "

But when I enter the information using "00\:00" that formula no longer calculates correctly. Not sure if there is an easy way to do what I what I am trying to do, but essentially whether I type " 0130" or "01:30" I want it to display "01:30".

Thanks for any feedback.

 

[FormR]

Hi, in my opinion you'd be much better entering the times properly with the ":", I think being able to directly work with the times outweighs the benefits of slightly faster entry.

That said, you could convert those numbers to times using a formula like this:

=TIMEVALUE(TEXT(A1,"00\:00"))

So, you'd replace the cell references for the "times" in you current formula with the formula above (obviously adjusting the A1 part).

 

[RoryA]

Replace your references to E287 and D287 with TEXT(E287,"00\:00") and TEXT(D287,"00\:00")

 

[PlumGr8]

Hi, in my opinion you'd be much better entering the times properly with the ":", I think being able to directly work with the times outweighs the benefits of slightly faster entry.

That said, you could convert those numbers to times using a formula like this:

=TIMEVALUE(TEXT(A1,"00\:00"))

So, you'd replace the cell references for the "times" in you current formula with the formula above (obviously adjusting the A1 part).

Do to the excel layout, this isn't an option for me currently, but I know overall that it does work.

 

[PlumGr8]

Replace your references to E287 and D287 with TEXT(E287,"00\:00") and TEXT(D287,"00\:00")

This worked perfectly, thanks for the assist. Have a great one