leading zero for mm:ss

Just use Custom Format

Formula:

Please Login or Register  to view this content.

Originally Posted by WhatEx

that I would like to represent as a text string like this:
[....]
19m49s
01h20m15s
Is there any formula that I could use to achieve this?

You indicate that you want two different formats, depending on the magnitude (less than 1h v. 1h or more).

If you have Excel time in a cell, you can use the following Custom format:

[<0.0416608796296296]mm\mss\s;[hh]\hmm\mss\s

If you want to format the Excel time in A1 as a string, you can use the following function:

TEXT(A1, "[<0.0416608796296296]mm\mss\s;[hh]\hmm\mss\s")

Caveat: Excel rounds fractional seconds. The constant 0.0416608796296296 corresponds to 59:59.5, rounded to 15 significant digits, which is the best we can do in a format specification.

That assumes that 19:49 is truly interpreted as 19m 49s by Excel. If you type literally 19:49, Excel interprets that as 19h 49m, and the cell is formatted as h:mm by default. You need to enter 0:19:49 or 19:49.0 in order for Excel to correctly interpret that as 19m 49s.

That would be a standard time format:

Formula:

Please Login or Register  to view this content.

Originally Posted by WhatEx

Since the cells contain the data as described what should I do to put the cell contents in 00:00:00 format?
Is there a formula that transforms the column of mixed formats (less than 1h or 1 hour or more)?
eg: 19:49 to 00:19:49

It is not an issue of formats. It is an issue with the interpretation of data; that is, the cell value. I think you know that.

In other words, as you indicated, you are addressing the issue that I raised with the interpretation of 19:49 as 19h 49m, when it was intended to be interpreted as 19m 49s.

-----

In general, there is no way to look at a cell value after data entry and know how it should have been interpreted.

Certainly, if the cell value is less than 1h, we can reasonably assume that it was entered and interpreted as intended, namely: minutes and seconds.

But suppose the cell value (not its appearance) is 19h 49m, which might be displayed as 19:49 or 19:49:00, depending on the cell format.

There is no way for anyone (except you, perhaps) -- or for any formula -- to know whether it was entered as 19:49:00 and even 19:49 and correctly interpreted as 19h 49m, or if it was entered as 19:49 and incorrectly interpreted as 19h 49m, but the intent was 19m 49s.

-----


That said, for any cell value that is 19h 49m, but you know it should have been interpreted as 19m 49s, you can enter a formula of the form:

=--TEXT(A1,"\0\:h:m")

into a parallel cell, then you can copy that cell value and paste-special-value into A1.

I use that formula instead of simply =A1/60 because the result of the latter might be infinitesimally different in some cases. So even though the cell value might display as intended, match and lookup formulas might fail or return unexpected results.

-----

However, suppose you know that any cell value that is intended to be hours-and-minutes will also have non-zero seconds. That is, instead of 19:49:00 for 19h 49m, it will always be 19:49:01 (19h 49m 1s) or more.

Frankly, I doubt that you can make any such assumption. But suppose you could.

Then, if the original data is in A1:A1000, you can enter the following formula into B1, copy down through B1000, then copy B1:B1000 and paste-special-value into A1:A1000:

=IF(OR(INT(TEXT(A1,"[h]:m:s")*24)=0, SECOND(A1)<>0), A1, --TEXT(A1,"\0\:h:m"))

I use TEXT(A1,"[h]:m:s")*24) instead of simply A1*24 in order to avoid any binary arithmetic anomalies, just in case A1 is calculated, not entered as a constant.

We cannot use simply HOUR(A1) because that returns zero for any number of hours that is a multiple of 24.

Originally Posted by WhatEx

In my case, it is quite rigid:
If there are 4 characters 1:23 then it is always mm:ss. [....]
If there are 7 characters 1:23:45 [...] then it is always hh:mm:ss. [....]
I apologise if I wasn't clear at the outset.

I think you have been very clear.

But I think you missed the point that I tried to make.

If you enter a 4-or-5-char time like 1:23, Excel will interpret that as 1h 23m, not 1m 23s as intended. Nothing you can do about that, AFAIK.

If you enter a 7-char time like 1:23:00, Excel will also interpret that as 1h 23m, as intended.

Now, looking at the cell value of 1h 23m, you cannot know if it was entered as 1:23 and should have been interpreted as 1m 23s, or it was entered as 1:23:00 and is correctly interpreted as 1h 23m.

AFAIK, there is no way to intercept and correct Excel's misinterpretation of 1:23 at data-entry time, even with an event macro or data validation.

AFAIK, the only way to avoid the ambiguity is to change the data entry, as I explained in my first response, to wit: 0:1:23 or 1:23.0 instead of 1:23.

But I can understand that you might not have any control over that.

PS...

Originally Posted by WhatEx

If there are 7 characters 1:23:45 (the hours never go into double digits)

Then there is the special case where if "hours" is 10 or more, that should be minutes.

So you could write a formula like the following in a parallel cell, then copy and paste-value back into A1:

=IF(INT(TEXT(A1,"[h]:m:s")*24) > 9, --TEXT(A1,"\0\:[h]:m"), A1)

(Note the correction in red. It applies to my previously-posted formula, as well.)

That covers times like 10:xx that should be interpreted as 10m (or more) xx seconds.

It also covers times like 9:xx:yy that are correctly interpreted as 9h (or less) xx minutes yy seconds, if yy is not zero.

But it still fails to correct 9:xx or less. Again, we simply cannot know if that was entered as 9:xx:00.

-----

PPS.... And if you expect zero seconds to be unusual, you might use a Conditional Format to flag suspicious cell values that have non-zero hours (presumably less than 10) and zero seconds after applying the conversion formula above. Then you can manually correct those cells after checking the original data entry to see if the cell value should be minutes-seconds instead of hours-minutes.

I say "if you expect [it] to be unusual" because otherwise, the CF might flag too many cells to be useful.

Select the range of cells, click Home > Conditional Formatting > New Rule > Use a formula, and enter the following formula (if the range starts with A2):

=AND(HOUR(A2)<>0,SECOND(A2)=0)

Note that I use the relative reference A2 instead of the absolute reference $A$2, which Excel might choose by default initially.

Continuing in the CF dialog box, then click Format > Fill, select a color, and click OK. Then click OK again to apply the CF.

Confirm that: (a) if you enter 1:23 into A3 (not A2, on purpose), the CF applies; (b) if you enter 1:23:1 into A3, the CF does not apply; and (c) if you enter 0:1:23 into A3, the CF does not apply.

We can use HOUR(A2) instead of the complicated INT(TEXT(...)*24) expression that I used previously because the conversion formula above ensures that the hour is not greater than 9.