Time Calaulation in numbers

Paul has the right answer, but forgot to mention 2 things.

1) time is actually decimal days in Numbers. Meaning you cant add 24, because you would be adding 24 days. you add 1. you would also want to test if Numbers is stills storing the date portion of the data. I don't think it just stores time typically, but just hides the date when showing time only. This would affect your calculation

2) you just need to test if the second time is less then the first. that would be the only time you would want to add the extra day. something like IF(A2<A1, A2+1-A1, A2-A1)

Jason

If you are entering time of day only, I recommend pre-formatting the cells as text so that there is no problem with the unseen date part of a "date & time" value. The formulas will work on text.

A formula that I am pretty sure does what you are asking is

=MOD(A2−A1, "24h")

where A2 is the end time and A1 is the start time

Alternatively, as suggested already, is to use the full date and time. In that case, simply subtracting one from the other will always give you the correct result.

Add 24 (or 48) hours to the second time, like this:

All cells are formatted as durations.

Paul.

Wayne's explanation is correct, except for this part:

When you enter a time only, Numbers adds the date portion for you (even if it's not shown) as the date on which you entered the time. Correct

When you enter a date only, Numbers adds the time portion for you (even if it's not shown) as the time at which you entered the date. Close.

When you enter only the Date part, Numbers sets the time part to 00:00:00. The resulting date & time value is midnight at the beginning of the date entered.

Regards,

Barry

Your right Barry, i meant to say when you type in time, time. It also adds the date portion, which might mess up the calculations you enter data across multiple days

Don’t know how I reversed that completely :)

thank you for correcting me, always appreciated sir.

jason

When you enter a time, you are only entering a portion of a value.

Numbers has a type call a "date / time" value. they are actually not separable. When you enter a time only, Numbers adds the date portion for you (even if it's not shown) as the date on which you entered the time.

When you enter a date only, Numbers adds the time portion for you (even if it's not shown) as the time at which you entered the date.

you would save yourself lot's of trouble if you entered the full date/time value. If you do, then the subtraction will work correctly AND the time will not be ambiguous

if you enter:

7:00 AM in one cell and 1:00AM in another cell, does the 1:00AM come before or after the the 7:00 AM? we'll you might answer that it obviously comes AFTER because the first cell (where we entered 7:00 AM) was the "start time" and the other cell was the "stop time".

recalling that numbers adds the date when you only enter a time, it you change the time on another day, then the date will also be changed for the time to the date on which you made the change.

enter the date/time values like this:

"12/12/2018 7:00AM"

Hi JL,

Reading this thread, every reply is correct.

Numbers has a Date & Time format that is an instant in time starting from midnight on the first day of January in the year 1904. Date & Time format automatically includes the Date and the Time (even if a cell does not display the Date & Time).

If you enter Start and Finish times on the same day, you are "crossing midnight" (the Dates are the same).

Example:

Don't worry about the red error triangles. We can fix them.

This is what is going on behind the curtain. Menu > Table > Unhide All Columns..

Columns A and B are now formatted as full Date & Time for my Region (weekday, day, month, year, 24 hour Time).

Formula in C2 (and Fill Down) =B2−A2

Formula in D2 (and Fill Down) =DUR2HOURS(C2)

Formula in E2 (and Fill Down) =IF(D2<0,"Edit the date!","OK")

Back to Table 1 as with hidden columns and 24 hour Time.

Click twice (not a double click) on a Start or Finish Time to show the full Date & Time display (Day Month Year and 24 hour Time in my Region).

Edit the Date of the actual day of Start or Finish.

Please call back with questions.

Regards,

Ian.

Hi JL,

Here's an example that may fit your needs:

J: Date & Time values, entered as time part only.

K: Date & Time values, entered as time part only.

Best practice is to enter each of these values (as time part only) on the day that the action occurred. The formula will also correctly calculate the duration for duty periods where actual on and off times are on consecutive dates, but both have been entered on the same calendar date, provided the time of day of 'off' is less than the time of day of "on" (as in examples in rows 3 and 4).

L: Formula to determine length of the duty period in hours and minutes.

This formula is shown below the table. Note that the durations to be added are enclosed in quotation marks, necessary to hold the unit label.

M: Minimum rest period, calculated with this formula:

M2: MAX(Table 2::$A$2,L2)

MAX compares the length of the duty period with the minimum rest time in cell A2 of Table 2, and returns the larger of the two values. The reference cell holding the fixed minimum rest duration is required, but may be an unused cell on the main table if desired.

N: Next available, calculated with this formula:

N2: K2+M2

This adds the duration of the rest time in M2 to the date and time value in K2 to determine the date and time when the person will next be available for duty. Note that correct calculation of the Date part requires that the "off" time is entered ON the date that the person goes off duty.

Regards,

Barry