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.
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.
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
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.....
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.