Excel FAQs: Hours and minutes

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

Also see the ExcelZone Compendium dates & numbers digest and Formatting section.

Register with AccountingWEB.co.uk and sign up for our free monthly ExcelZone eBulletin to receive regular updates.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.