Time format in formula bar not changing

Hello n180093,

The difference you are seeing all comes down to cell formatting. What is displayed in the formula bar is how the data was entered into the cell. What is displayed in the cell is detemined by the cell formatting.

In your sample file cell, "D4" is formatted as "General", so what is displayed in the formula bar and cell are the same. Cell "D5" is formatted as "Custom" (h:mm) so the formula bar displays what was entered (8:00:00 PM) and the cell displays that as per the formatting (20:00)

When working with time values the cell values must all be in the same time format; using a general format will not give the desired results. Make sure all of the cells are using the same custom format and you calculations should work out.

Cheers   :-)

n180093

Your revised file shows that the issue isn't the cell format (how Excel displays the values) but the values themselves.

By this I mean that Excel stores times as a number so, for example, 0.5 equates to midday (12:00 on the 24 hour clock). If I run a small macro to show what's in cells B4:B13, the results are (address then value, format):

• B4: Value: 0.583333333333333 Cell Format= h:mm
• B5: Value: 0.75 Cell Format= h:mm
• B6: Value: 0.916666666666667 Cell Format= h:mm
• B7: Value: 10:00 Cell Format= h:mm
• B8: Value: 16:00 Cell Format= h:mm
• B9: Value: 12:00 Cell Format= h:mm
• B10: Value: 19:00 Cell Format= h:mm
• B11: Value: 0.5 Cell Format= h:mm
• B12: Value: 12:00 Cell Format= h:mm
• B13: Value: 12:00 Cell Format= h:mm

and you can see that although they're all set to display as h:mm, only B4:B6 and B11 contain numbers, the rest are (presumably created or imported) as text. Therefore those cells won't work in numerical calculations. Specifically the MIN and LARGE functions will NOT see those cells until you change and enter them (and Excel see them as number). E.g.  change B10 from formula bar 19:00 to 09:00 and the formula bar will show subsequently show as 19:00:00 (hh:mm:ss) and appear as the MIN.

Your "overwritten" values ARE numbers, so the same macro gives numbers against all cells:

• H4: Value: 0.583333333333333 Cell Format= h:mm
• H5: Value: 0.75 Cell Format= h:mm
• H6: Value: 0.916666666666667 Cell Format= h:mm
• H7: Value: 0.416666666666667 Cell Format= h:mm
• H8: Value: 0.666666666666667 Cell Format= h:mm
• H9: Value: 0.5 Cell Format= h:mm
• H10: Value: 0.791666666666667 Cell Format= h:mm
• H11: Value: 0.5 Cell Format= h:mm
• H12: Value: 0.5 Cell Format= h:mm
• H13: Value: 0.5 Cell Format= h:mm

One easy way to fix those text values is to put this is F4

=1*B4

then drag or copy it down to F13. Now select F4:F13, copy then select B4 and ribbon Home/ Paste (down) Paste Values/ Values(V).

B4:B13 will now be times so you can clear F4:F13. Likewise for your larger sheet.

Hope this helps. If so, please remember to mark this Answer as Selected.