Controlling Excel time format input/output

Question

Background: I have been officiating our local jogging events for about ten years now. I am responsible for handling the data of the participants (name, sporting club, bib number) split into their categories (age bracket+gender, distance). The main task is collecting their times, and processing that data (sorting the runners within their category etc). I can handle this with Excel mostly fine.

Problem: What is the ideal time format for entering the race times of the participants? The times are either in the format mm:ss or (for slower runners and/or longer distances) h:mm:ss. Excel doesn't seem to have a built-in format where the hours field is optional. For optimizing my workflow ideally I would like to have a cell format such that the input 47:12 is to be interpreted as 47 minutes and 12 seconds, and the input 1:09:38 is to be interpreted as 1hr 9 minutes and 38 seconds. However, Excel, with the best fitting cell format that I found, will insist that the input 47:12 means 47 hours and 12 minutes. For times exceeding 1 hour I would input 1:03:00 if I meant that the seconds field is to be left with value zero.

How to make Excel realize that when the format can handle up to three numbers as inputs, it would, when given only two numbers, move them towards the end?

Thinking: I "can" key in 47 minutes and 12 seconds as 0:47:12 all right. But because most of the times are under 1 hour, that is partly wasted effort. Also, using such a format the data is displayed on the screen together with that superfluous 0:. What's worse (IIRC) those leading zeros also appear in the printed versions, which is strange (insulting even) in a shorter distance for junior participants.

My hack: I enter the times as general numbers in the mm,ss format (in these parts a comma serves as a decimal separator). Excel can sort those as numbers just fine. I then duplicate the data of that sorted column to another "printable" version (formatted as text), where the data is just copied, but I correct the times exceeding 60 minutes by hand. This works just fine as long as I'm not in a hurry (our event is not exactly Boston Marathon, say, less than 200 participants), and remember to hide the column that is not supposed to be printed. This is kludgy, and there have been accidents, when other officials have been rushing me to get the results printed.

Answer 1

I managed to create a format where the hour-field is optional. It works with a conditional format. First you format your cells as standard, so you get the times as comma-values. After that you create a conditional format for these cells, which has two rules:

1. if cellvalue > 0.04166667 format hh:mm:ss
2. if cellvalue < 0.04166666 format mm:ss

Result:
47:12
01:09:38
01:00:00

So you get what you really want and you can use the original values for sorting and so on.

EDIT:

For the input you need four additional columns. You enter the times as you want, e.g. 47:12 and 1:09:38. In the next three columns you split these values in hour, minute and second, whereby the interpretation limit is 3 hours (03:00), which is 0.125.

So, these are the formulas for the split columns (your input is in B1):

Hours: =IF(B1>0.125,0,HOUR(B1))
Minutes: =IF(B1>0.125,INT(B1)*24+HOUR(B1),MINUTE(B1))
Seconds: =IF(B1>0.125,MINUTE(B1),SECOND(B1))

And finally, you put all values togehter in the forth column:

=TIME(C1,D1,E1)

and use the conditional format above.

Answer 2

I really like IQV's answer above, but as pointed out in the comment section, the leading zero will be required for the data entry side. If for whatever reason this is not acceptable you can use the following ugly formula to convert your time entered in your usual method of mm,ss to hh:mm:ss with the hh: being displayed as required. Unfortunately it converts the whole thing to text which means you can no longer perform math operations on it.

=IF(FIND(".",MOD(D2,60)&".")=2,"0","")&MOD(D2,60)

and since you use , as your decimal separator the formula would become:

IF(FIND(",",MOD(D2,60)&",")=2,"0","")&MOD(D2,60)

If you use ; as your list separator then your formula becomes

IF(FIND(",";MOD(D2;60)&",")=2;"0";"")&MOD(D2;60)

There are probably some cleaner formulas, but that will get you started. Just replace D2 with the location where your time is stored.

Again I still prefer IQV's answer as you can do much more with the time information when its stored as a number and not text.

Option 2

lets say you change your data storage method to hhmm,ss in cell D6. you could rip apart the information and reassemble it in a display friendly version as follows.

=IF(FIND(".",D6)<=3,LEFT(D6,2)&":"&RIGHT(D6,LEN(D6)-FIND(".",D6)),LEFT(D6,FIND(".",D6)-3)&":"&MID(D6,FIND(".",D6)-2,2)&":"&RIGHT(D6,LEN(D6)-FIND(".",D6)))

you will need to substitute your list separator for the , and then substitute a coma for the decimal.

Answer 3

If you will be entering your data as

`mmm,ss`

where the comma is the decimal point, then you can convert it to "Excel Time" with the simple formula:

=DOLLARDE(A1,60)/1440

Format the result as you wish.

If you want everything displayed as h:mm:ss then use that as your custom format (Format > Cells > Number > Custom Type:...)

If you want h to be displayed only with values of 60 minutes or greater, then use

[<0.0416666666666667]mm:ss;h:mm:ss

for your cell's custom format.

Beware that seconds must be entered with two digits always. In other words

6,2   will translate to 6 min 20 sec.
6,02  will translate to 6 min  2 sec