Hours & minutes time entry handling in Excel 2

[shawnmandel]

I'd like to be able to enter a number of minutes into a cell, but if I enter anything more than 60 minutes, I would like it to recognize this and enter the appropriate format in hours & minutes.

For example:

If entering 75 minutes, the cell should automatically display 1h 15min. Also, if I have a cell with a formula, it should be able to calculate, for example, the difference in time appropriately displaying the result in the stated hours & minutes format

Any assistance is much appreciated.


Regards,
Shawn

 

[SkipVought]

Hi,

It's difficult to override Excel's parsing of date/time entry.

enter your minutes in column A

In column B, assuming that we're in row 2...

format bolumn b as TIME [h]:mm
[tt]
=A2/24/60
[/tt]


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[shawnmandel]

Hi Skip,

It works for me!

Thanks,
Shawn

 

[shawnmandel]

Now what I need is a system to enter time without having to make any calculations. For example,

If a process took 6 min 52 seconds, is there a way to enter this time into a cell so it will be recognized/converted to this time?

For example, if I enter 00:06:52, I`d like it to recognize this as a time format and it should appear as 6m 52s.

Similarly, if a process took 1 hour 37 minutes, I would like to be able to enter 01:37:00 with Excel appropriately recognizing it as time.

Thanks,
Shawn

 

[SkipVought]

Excel does recognize those entries as TIME.

Here's what Excel does...

You enter a value like 00:6:52

This is in rality a STRING of TEXT CHARACTERS that Excel recognizes as your intention to enter a TIME value.

Excel parses the string into and HOUR, MINUTE and SECOND value and CONVERTS these values to Date/Time using the TIME Function
[tt]
=TIME(HOUR< MINUTE< SECOND)
[/tt]
In the case of 00:6:52 the value is 0.004768519, since TIME is exresses in FRACTIONS of a DAY.

Excel does a similar thing with DATE ENTRIES, where it parses the input string into YEAR, MONTH & DAY, converting this to a WHOLE NUMBER, TODAY, for instance, being 38386.


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[AnotherHiggins]

As Skip said, entering the data in the way you asked about (00:06:52 or 1:37:00) will automatically be recognized as a time. But if your question is about how to format the cell to look like "6m 52s", then you could use a custom format.

Highlight the range you want to change, go to Format > cells then, on the Number tab select custom from the list on the left. In the Type field on the right, type [COLOR=blue white][h]"h" m"m" s"s"[/color]

I don't know of a way to make the format change from "6m 52s" to "1h 57m" automatically based on the time entered, but someone here might be able to figure that out. What I offer above will display the values in your examples as "0h 6m 52s" and "1h 57m 0s", respectively.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SkipVought]

John,

I COMPLETELY missed that nuance!

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[shawnmandel]

Thanks Skip & John.

This more than answers my questions. As for automatically changing format from "6m 52s" to "1h 57m" based on the time entered, it would've been nice, but not necessary at this point.

Cheers!

Shawn