Excel - Pad time with 00: if not entered

[ForumKid1]

I have my column formatted as [hh]:mm:ss and all is great if I enter a time as 01:55:22 which is the amount of time it took me to do the activity. So I want users to be able to enter 45:01 for 45 minutes and 1 second. I dont want them to have to enter 00:45:01. If the leading zeros and colon are not entered, the column is converted to 45 hours, not minutes.

So what I'm trying to do is automatically pad the 00: to the cell if no hours are entered. I guess if the LEN of the column is < 5. Not really sure on the correct solution here.

 

[SkipVought]

hi,

You have to understand how Excel CONVERTS certain data that is entered.

If you type
[tt]
1:
[/tt]
and hit ENTER, excel parses the TEXT that you entered and ASSUMES from the structure of the data, that you intend to enter a TIME value, parsing the data so that the numeric part before the COLON is assumed to be HOURS. The parsed text is converted to a TIME VALUE, just as if you entered the formula
[tt]
=TIME(1,0,0)
[/tt]
so that the result value is
[tt]
0.041666667
[/tt]
and that number is FORMATTED in a default TIME FORMAT.

So the bottome line is that there is nothing you can do to force Excel to interpret your 45:01, to be anything else other than 45 hours 1 minute.

Now you could enter 45 in one column and 1 in the adjacent column and use the TIME() function to return the value that you want in another column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ForumKid1]

Thank you. I thought there was some way to append a 00: to the cell value in case the length was less than 5. Apparently not .

I know I can split it into multiple columns. Wanted to avoid that. Makes my worksheet hard to read and more cumbersome to enter, etc.

Thank you very much for your help. I guess I'm just left with having to enter the 00: if the time is less than one hour.

 

[SkipVought]

append a 00: to the cell value

Append is a TEXT manipulation.

Time is NOT TEXT!!!

What you SEE in the cell, that APPEARS to be text, is merely a FORMAT.

Look at a real time value in the CELL and in the FORMULA BAR and observe the dirfference.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ForumKid1]

I see that now. I certainly could make the columns text...well then I would lose my ability to sum at the total section so that would not work.

 

[SkipVought]

You COULD make your input data text, but then your data would be useless, unless you would CONVERT the text to time in another column.

You just have to work with the tool that you have, understanding the advantages and limitations.

So you NEVER EVER enter HOURS? (reminiscent of HMS Pinafore)

"What, never?"
"No, never!"
"What, never?"
"Well, hardly ever!"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

Well, you can skip some 0s. Instead 00:03:05 you can enter 0:3:5, instead of 01:00:00 just 1:0 or 1:. Anyway, you need to start with a digit for hours and next use one or two colons, in the latter case separated by minutes.

combo

 

[ForumKid1]

Half the time hours are entered and half the time they are not

I guess I could use a text column. Convert to time in a hidden column. Then run my totals of the hidden column.

 

[SkipVought]

I certainly do not recommend this approch, because it is wrought to other perils, but it answer your SUM THE COLUMN OF TEXT VALUES issue.

1) In an EMPTY column (it MUST be empty, with no existing time values) FORMAT the column as TEXT.

2) Enter your MINUTES : SECONDS values

3) use this ARRAY formula (shift+ctrl+enter)
[tt]
=SUM(TIMEVALUE("00:"&G:G))
[/tt]
assuming that your TEXT COLUMN is column G


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You could try to use data validation (time format) in the users column. The advantages:
- possible entry info about required format,
- you can limit time in a cell to a given range,
- possible message if wrong data (text or out of scope).

combo