A new series of articles offering tips and tricks to conquer common Excel problems starts with time formats and calculations. Buried within the vaults of AccountingWEB are numerous Any Answers queries and tutorial articles that tackle Excel problems that catch people out. One of the most common is how to format dates and times - particularly if you want to use them for calculations, for example in a timesheet. Excel time/date system To start from the beginning (which for Excel is 00:00:00 on 1 January 1900), Microsoft explains that its spreadhseet has been designed to store all dates as integers and times as decimal fractions. This set-up allows Excel to add, subtract, or compare dates and times like any other number. Keep these points in mind:
- To display the value as a number of days, or as a fraction of a day for times, simply choose Format, Cells and choose a number format.
- To enter 45 minutes in a cell formatted to display a time, you need to enter the zero number of hours, so 0:45,
- If you are working with times, and need a value in decimal hours, multiply the result by 24 to convert days to hours.
Useful time functions Excel comes with a set of ready made functions to simplify complex date and time calculations, including:
- TIME - converts hours, minutes and seconds to a serial number value
- TIMEVALUE - converts conventional time format text into an Excel time value
- NOW - returns the serial number the date and time the function is invoked.
Other topics covered:
- Working with dates and times
- Dealign with time values over 24 hours
- Hourly charge calculations
Register with AccountingWEB.co.uk and sign up for our free monthly ExcelZone eBulletin to receive regular updates.