Converting time to hours in numbers

Hi Stevie,

Like this:

Formula in D2: C2−B2

Start with a table formatted as Automatic. As you enter times in columns B and C, Numbers will format those cells as Date & Time. Column C becomes Duration format.

Take a look at the "Sign in" table in this template (New from Template Chooser):

Call back with questions. Regards,

Ian.

I, myself, don't necessarily know how to do it...but this guide may help!

Calculate values using data in table cells in Numbers on Mac - Apple Support

Check "Function Browser"

Hi Stevie,

Date and Time calculations (and data entry) can be puzzling in Numbers.

If a cell is displaying a Date and Time, just a Date, or just a Time of Day, the cell actually holds a Date and Time value, marking a specific instant in the flow of time.

Having that Date value in the cell can lead to several head scratching sessions before the user reaches the AHA! stage. Here's a Table using the start and finish times listed in your post above.

All cells in column B contain the same Date and Time value — 8:00 in the morning on the 14th of March, 2023. In each cell, only the Time of Day part was entered, and the Date of entry was automatically entered to complete the D&T format.

When the earlier D&T is subtracted from the later D&T, the result is a Duration, expressed in hours and minutes, as seen in rows 2 and 3 of Column E. This result is the same as th one in your example, but is expressed as hours and minutes, separated with a colon.

In Row 4, the DUR2Hours function ( DURation to Hours ) is used to convert the hours and minutes values in the result to a number representing the number of hours and the fraction of an hour equal to the 30 minutes, expressed as a whole number and a decimal fraction part.

Very simple formula, but it does take a while to get the D&T concept into the 'got it' phase.

Looking ahead: Another oddity, it your not familiar with it is in calculating the hours of a shift that crosses the midnight boundary. If the State and End D&T values each contain the correct date, all should be well. If both have the SAME date, Numbers will correctly complete the calculations, but the result will not be correct.

When the user enters only the Time part of a D&T value, Numbers sets the Date part to the date on which the entry was made. If both entries are made on the same day, Start and End will both have the same Date, and in most cases, this means the shift, as recorded Started at, say 9:00PM and finished at 4:30AM the same day.

For shifts crossing the midnight boundary, you'll need to either enter the full Date and Time of both the Start and Finish, or take other measures to ensure the result will be correct.

Regards,

Barry

And if you need your hours as a decimal number for further calculations (perhaps multiplying by an hourly rate) then use DUR2HOURS, something like:

DUR2HOURS(C2-B2)

SG