Changing numbers to hours

[simonsm664]

I have two questions... Easy one first. When I format a cell as time (13:30), why does it go to 00:00 when i enter "2." I want it to go to 2:00, but the only way to do it is enter the colon myself... there must be a way to enter "2" and have the cell show "2:00."

Second question: I am trying to compare an estimated time to actual time. The problem is my estimate is 4-5 hours. How can I make 4-5 hours show up(in number format) in one cell... is that even possible or do I need to make two cells, one for min time and one for max time?

 

[sammich]

Auto format detection is on.

Just change the format of the cell to a time one.

Not sure if it applies to all spreadsheet apps:

=CONCATENATE(LEFT(low estimate cell, 1), "-", LEFT(upper estimate cell, 1)

Look up the function definitions for more info on the functions. (LEFT(cell, number of characters from the left to take))

 

[simonsm664]

Thank you for the help, but I may have asked the question wrong. In column 2 I have cells with time ranges (i.e. "4-5")... representing 4-5 hours to complete a project. In column 3 I have the actual amount of time it took (i.e. "4:37"). In column 4 I want to show how far off my estimate is from the actual amount of time.

 

[sammich]

Thank you for the help, but I may have asked the question wrong. In column 2 I have cells with time ranges (i.e. "4-5")... representing 4-5 hours to complete a project. In column 3 I have the actual amount of time it took (i.e. "4:37"). In column 4 I want to show how far off my estimate is from the actual amount of time.

=LEFT(estimate, 1)
=RIGHT(estimate, 1)

If you set the format right then

=actual-LEFT(estimate, 1) will give you the right time. Just experiment, and look up the function references.

 

[simonsm664]

Thanks for the help!