Excel 2007 : Time format

[Gabety]

I am new user to Excel. I have 2007. I am trying to format a cell to show the time as hh:mm

I do not want the system time used. I want to be able to enter any time I desire but in order to save time be able to type in four digit and then have it auto fill/complete with the colon. No matter what I enter and then move to another cell the time always completes with 00:00.

I'm getting really frustrated and wonder why this won't work as I want it to. Any ideas?

[sweep]

Hi,

Use a helper column. This formula

=IF(LEN(A1)<2,":0"&A1,LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))

will convert your 4 digit time into std notation.

[DonkeyOte]

If you want to enter 1450 and have it "appear" as 14:50 then you can use a Custom Format of: 00\:00

Note though that the underlying value remains 1450 which in XL terms is 1450 days so if you wish to conduct calcs on these values down the line you will need to accomodate accordingly.

Alternatives are

a) use a helper to translate the 1450 to 14:50 Time (decimal) - eg:

Please Login or Register  to view this content.

b) use VBA to override values as they are entered


If you do opt to use just the custom format you can total etc along the lines of the TEXT approach within a SUMPRODUCT, eg:

Please Login or Register  to view this content.

would sum non-time values in A1:A10 as time values.

[Gabety]

If you want to enter 1450 and have it "appear" as 14:50 then you can use a Custom Format of: 00\:00

This worked great and exactly as I wanted. Thanks all, the formulas are way over my head and the cell format really only needs to be as simple as this suggestion. I'll be back I'm sure with other questions.

[rwgrietveld]

Gabety,

Note that the format might be 14:50, the value is still 1450. This in case you would like to calculate with this time later on. Then you have to use the other two solutions of DO.

[Gabety]

Here is another simple one that I just can not whip or find the answer to in the help files.

I have merged 5 cells together. They are formatted for text, center allign, word wrap. Some times one line of text will do and another 2 or 3 lines will be necessary. I want the merged cells to expand down the page as lines are added through word wrap. They currently expand upwards and behind the frozen pane at top of page.

When expanding I want the whole row and it's other cells to expand to match the cell with text. Make sense?

[DonkeyOte]

new question, new thread please...