Trying to build a file with Track meet times

[John Guest]

Hi

I am building a SS with track race meet times, the example I have is 2:28.60
Meaning 2 hours 28 minutes and 60 seconds. When I format it is keeps turning into time 12:02:29 AM even though it displays ok in the Cell. I want it not to change to a date but keeping it as text will not work as I need to find fastest times etc.

Any help gratefully appreciated

 

[Logit]

Fist, format your cells as TIME / 13:30:55

Then enter all times as hh:mm:ss

Using your example above, enter as 2:29:00 (60 seconds is the same as a minute, so enter it as the next minute instead of 60 seconds.)

Another example : 2:29:37 ... or ... 11:05:23

 

[John Guest]

This displays the item in the cell correctly
00:05:07.5 but the value in the cell is still 12:05:07 AM

How do I get it to not just display right but not do the time conversion

 

[Logit]

I don't know how to describe the process Excel uses regarding time and dates. Just know that your viewed result in the cell is correct as will be your computations when adding or subtracting times. What you see in the formula bar can be ignored .... the displayed cell result is what you are wanting.

You can hide the formula bar if necessary.

 

[Khalid NGO]

Hi John,

I echo what Logit said:

What you see in the formula bar can be ignored .... the displayed cell result is what you are wanting.

Excel is very smart, it automatically change the cell to display values based on user inputs.

For example if you type 1-Jan-2018
Cell will be automatically shown as 01/01/2018, where as the actual cell value is 43101

If you type $43101
Cell will be shown as $43,101

Same situation you are facing for time values.
But you can force excel to keep the format by converting your cells to Text, and then use a helper column for calculation purpose with simple cell reference e.g. =A2+0

Or if you don't want to use helper column, try array formula like:
=MAX(A1:A10+0)
With Ctrl+Shift+Enter (not just enter)

Regards,