For a simple Excel sheet time reporting, i needed to enter hours every day for arrival and departure time.
Excel is not so smart about the time format. For instance I need to enter “9:00″ for nine in the morning. I wanted just to enter “900″, and let the excel sheet format the data correctly itself.
I found a working solution on PC Mag: “Easy Date and Time Entry in Excel Tips“.
- Enter time format as a simple number (like “900″ or “1500″).
- Format the cell to display an extra colon.
- In a (hidden?) cell, calculate the time to use for further calculations.
Format cells for viewing:
Take the cell and format it so it visually shows the colon for the number that is input. Go to the menu item “Format” > “Cells…” dialog. Select “Custom” format and input “0\:00″ as shown on the screenshot.
Create (hidden)? cell to calculate time:
Take an adjacent cell and let it interpret the input value as a time value.
is equivalent to 09:15:00 and will require 3 parameters for hour, minutes, seconds.
Our inputformat is “915″. Extract hours value:
Extract minutes value with modulus that extracts the rest 15 from 915 divided by 100:
So the formula will be
This calculation is hidden in the final spreadsheet in columns E, G, and J.
Here a screenshot of the result:
I added the time sheet template here for further convenience.
2011-timesheet.xls (47KB Excel format)
(timesheet updated January 6th: Sum can now handle more than 24 hours)
IAnd now I can happily save entering colons every day :)