Time Value & Display

[lstewart]

Hi Everyone,
In Excel cell I have entered the time 13:00 and this is displayed in the cell correctly. However, I can see the cell value in the display bar is 13:00:00. I need both to display 13:00. I have tried format cell > time > custom and select hh:mm but nothing changes.
Thank you for your help.

 

[jasonb75]

The format of the bar can not be changed.

edit:-

Actually, that's not entirely true. You can format the cell as text, but it will most likely break any formulas that need to work with your times.

 

[Fluff]

Changing the format of the cell, just changes how the value is displayed, it does not change the value.
If you want to keep it as a Time value then the formula bar will show hh:mm:ss

 

[lstewart]

If I change for cell format to text, it displays 0.541666667. I don't need to keep the time format and I'm not using any formulas. I need the value and display to be 13:00

 

[jasonb75]

You are wanting the impossible, what I suggested was a very crude workaround which will show do what you want, but will cause more problems than it is worth. No experienced excel user would even consider attempting it.

Specifically why do you 'need' to change what is shown in the formula bar?

 

[lstewart]

I'm importing this data into another piece of software. When the import is complete the other software shows 13:00:00 the same as the value. I need everything to display as 13:00. I'm not using any formulas, I don't even need it to be a time format.

 

[Fluff]

In that case just set the cell to Text & then insert 13:00

 

[jasonb75]

In that case the text format shouldn't be an issue, but any existing records will be converted to time serial (the number that you saw earlier).

To convert your existing data, select all of the cells that contain times then run the code below. Please create a backup copy of your file before testing the code.

Sub TimeToText()
Dim c As Range
For Each c In Selection
    c.NumberFormat = "@"
    c.Value = Format(c.Value, "hh:mm")
Next
End Sub

 

[lstewart]

Thank you all. I have over 300 rows that need changing to text then retyping, so I was looking for an easy way. Thank you again, much appreciated.

 

[Fluff]

That's what Jason's code will do.