Excel Time Format

[EvianBaby]

I'm creating a time sheet for someone and have colums of time on site and time off site. I've tried to format the colums so that if he types 9 then it formats it to a time.

I highlighted the cells and seletect the 'time' format but every time I enter a number it just comes up as 00.00.00

When I remove the format the number I typed in appears.

I can't work out what I'm doing wrong?

[MarkRae]

Firstly, you can't do what you need quite so easily as typing 9.

You need to format the cell(s) to something like hh:mm or HH:mm if you want a 24-hour format.

But the user will still need to enter e.g. 9:30

This is to do with the way Excel stores and processes dates and times.

[EvianBaby]

OK, thanks.

Can you tell me how to do that then? Regardless of what I type it still just fills the cells with 00:00:00.

[MarkRae]

Right-click the cell in question, click Format Cells, select Time and pick one of the formats. Or, if you don't want to display seconds, select Custom and add HH:mm as a custom format.

[EvianBaby]

This is very weird, I tried that after your last response and a few others too but I still get the same result. I selected the hh:mm format.

If I enter 9.30 it very oddly shows the time of 07:12 in the cell. In the function bar at the top is shows - 09/01/1900 07:12:00

It's showing some sort of date but I've only selected a hh:mm format. Confused.

[rojash]

Excel will only recognise the input as a valid time if it contains at least one colon
So to enter 9 hours, he will have to enter 9:
to enter 9.5 hours, 9:30
to enter half an hour 0:30

[ChuckFickens]

Never understood why hh:mm is under custom and not time.

[MarkRae]

Once again...

You need to enter e.g. 9:30 - that's nine, followed by a colon, then a three and a zero.

[HowardKennitby]

which version Excel are you using?

[ChuckFickens]

Shall I say it too....

Use a : not a .

:)

[EvianBaby]

Oh bugger, yes sorry. My mind skipped that : part.

I think I'll just have to leave it with no format then, I can't see him doing it the way it needs to be done.

[MarkRae]

Indeed. 9.30, as far as a date / time formatted cell is concerned, is 9.3 days.

[EvianBaby]

Oh, and of course thanks for the help!

[rojash]

...this is because Excel stores times as fractions of 24 hours

[MarkRae]

> I can't see him doing it the way it needs to be done.

Seriously? He can't manage four keystrokes...?

[EvianBaby]

I'm sure he could work out how to do, just that he won't be bothered to. And I do hate a messy spread sheet.

[MarkRae]

^^ Fair enough. Computers aren't for everyone...

[EvianBaby]

Quite, which is why I'd hoped to keep it simple enough for him just to type the minimum. Never mind.

[ChuckFickens]

Put a list of times on an unused work sheet in steps of what ever you want and then create a drop down list of times based on that list in the places you want them to enter the time data, so they simply have to select the time they want rather than typing it in.....

[EvianBaby]

So, if I've created the list of times in the following sheet....... you know what the next question is.... :)

[ChuckFickens]

Click in the cell you want the list in, then click on the data tab and select data validation, then set the validation criteria to allow a list and set the source as the range of cells you created your list of times in.

Also make sure you format the cell with the drop down as a time cell.