Numbers Time Sheet Doesn't Work

Hi 'dad,

"I can only get the "Total Hours" column (D18) to correctly total up hours worked if ALL the rows above it (D2-D17) contain time values in them."

Actually, NONE of the cells in column D will contain "time values." Nor, for that matter, will the cells in columns B and C ever contain "time values". Cells in B and C will either be empty, or will contain a Date and Time value. You may format these cells to display only the "time part" of a Date and Time value, but the date part and the time part are always both included in the value in that cell.

Cells in column D contain the results of a subtraction (C-B). The type of value depends on what type of values are in C and B.

• If both C and B contain Date and Time values, the result will be a Duration.
• If B contains a Date and Time value, and C is empty (or contains a number), the result will be an error message (see below).
• If C contains a Date and Time value and B is empty or contains a number, the result will be a Date and Time value as many days (and fraction of a day) earlier than the Date and Time value in C.
• If both C and B contain numbers or both are empty, or one contains a number and the other is empty, the result will be a number.

Here are four examples, using data from the first four rows of your table, All cell formats are "Automatic":

The error message you got (different from the one shown here) explains why the SUM formula is generating an error message—all of the values in column D are durations except the value in D6, which is a number. Ian's solution inserts a null string into column D in any row where nothing has been entered into either or both of columns B and C. Even though a null string is 'empty', it is read as a text value, and is ignored by SUM. All of the cells that SUM is paying attention to then contain duration values, and will SUM correctly.

Regarding your second issue: Conversion of duration to a number representing hours (and fractions of hours requires only enclosing the formula in column D in a DUR2HOURS statement. Here is the same table as above, with the revised formula. Don't be concerned that three cells now have error triangles, where only one had an error before. The first is the same error message as shown above, the second is shown below, and the third is similar to the second (but says it found 0).

Ian's test for empty cells will also work here:

One thing that puzzles me regarding your original table is the correct results in rows 11 through 17, where work begins on one (calendar) day and ends on the next calendar day. In row 11, for example In is 13:00 and Out is 0:15. If both these values were entered on the same calendar day, the date part of both Date and Time values would be the same, and the expected result would be -12:45 (for C-B) or -12.45 (for DUR2HOURS(C-B). To get the correct result (11:15 or 11.25) would require entering B on one day and C on the next (calendar) day, or would require a formula that would make the correction when needed.

It's also possible that Numbers for iOS has the built in smarts to make this adjustment where the data suggests that the work period crosses the midnight boundary.

Regards,

Barry

Hi cc,

"It is better to use =IFERROR(MOD(TIMEVALUE(G2)−TIMEVALUE(E2),1)×24,0). This will work for up to 23.75 hours, doesn't matter if you clock-in PM and clock out AM, PM to PM, and AM to AM, and every which way you can think of."

Interesting. I'd noted the issue, but didn't make much of it as superdad had included PM to AM examples with the correct results.

Good thinking on the use of IFERROR to handle the empty cells issue. I was surprised that MOD resulted in a correct 'hours worked' value for PM to AM shifts as I hadn't done any investigation of the modulus when the quotient was negative. Something new to file away for future use.

Thanks for chiming in. I agree with SG—yours is the simplest and most elegant solution.

Regards,

Barry

Hello

Apart from the error you're seeing which can be easily suppressed by appropriate IF() statement, you should be very careful when processing time value in Numbers. Actually there's NO time-only value but always date-time value in Numbers. And worse, if you input time without date, date is assumed to be the date of the input.

I'd classify it as bug or bug-like behaviour of the application because if you input date without time, the time is assumed to be 00:00:00 and not the time of the input. Therefore the expected behaviour of time input is that if you input time without date, the date should be assumed to be the constant epoch date, which is 1904-01-01, and not the date of the input. If user inputs time without date, date part should be assumed to be null or at least some constant value.

But the application behaves differently. The problem would arise when, for instance, you edit the quasi time-only value on a later date and the calculation yields unexpected result. Since you're not showing date part of the date-time value in question, it would be hard to realise the cause of the problem.

Reasonable workarounds would be a) to use TIMEVALUE() function to extract the true time-only value from date-time value or b) to use special time notation, such as HHMM instead of HH:MM, which Numbers does not interpret as date-time value.

E.g. a) using TIMEVALUE():

Table 1 A1 date A2 2015-07-01 A3 2015-07-02 A4 2015-07-03 A5 2015-07-04 A6 2015-07-05 A7 2015-07-06 A8 B1 in B2 2015-07-21 07:30:00 B3 2015-07-21 08:00:00 B4 2015-07-21 13:00:00 B5 2015-07-21 14:00:00 B6 B7 2015-07-21 07:00:00 B8 C1 out C2 2015-07-21 14:30:00 C3 2015-07-21 17:00:00 C4 2015-07-21 00:15:00 C5 2015-07-21 02:00:00 C6 C7 2015-07-21 12:00:00 C8 D1 hours D2 =DURATION(,IF(LEN(B2)*LEN(C2)>0,MOD(TIMEVALUE(C2)-TIMEVALUE(B2)+1,1),0)) D3 =DURATION(,IF(LEN(B3)*LEN(C3)>0,MOD(TIMEVALUE(C3)-TIMEVALUE(B3)+1,1),0)) D4 =DURATION(,IF(LEN(B4)*LEN(C4)>0,MOD(TIMEVALUE(C4)-TIMEVALUE(B4)+1,1),0)) D5 =DURATION(,IF(LEN(B5)*LEN(C5)>0,MOD(TIMEVALUE(C5)-TIMEVALUE(B5)+1,1),0)) D6 =DURATION(,IF(LEN(B6)*LEN(C6)>0,MOD(TIMEVALUE(C6)-TIMEVALUE(B6)+1,1),0)) D7 =DURATION(,IF(LEN(B7)*LEN(C7)>0,MOD(TIMEVALUE(C7)-TIMEVALUE(B7)+1,1),0)) D8 =SUM(D)

E.g. b) using HHMM notation:

Table 2 A1 date A2 2015-07-01 A3 2015-07-02 A4 2015-07-03 A5 2015-07-04 A6 2015-07-05 A7 2015-07-06 A8 B1 in B2 730 B3 800 B4 1300 B5 1400 B6 B7 700 B8 C1 out C2 1430 C3 1700 C4 15 C5 200 C6 C7 1200 C8 D1 hours D2 =DURATION(,,IF(LEN(C2)*LEN(B2)>0,MOD(INT(C2/100)+MOD(C2,100)/60-INT(B2/100)-MOD(B2,100)/60+24,24),0)) D3 =DURATION(,,IF(LEN(C3)*LEN(B3)>0,MOD(INT(C3/100)+MOD(C3,100)/60-INT(B3/100)-MOD(B3,100)/60+24,24),0)) D4 =DURATION(,,IF(LEN(C4)*LEN(B4)>0,MOD(INT(C4/100)+MOD(C4,100)/60-INT(B4/100)-MOD(B4,100)/60+24,24),0)) D5 =DURATION(,,IF(LEN(C5)*LEN(B5)>0,MOD(INT(C5/100)+MOD(C5,100)/60-INT(B5/100)-MOD(B5,100)/60+24,24),0)) D6 =DURATION(,,IF(LEN(C6)*LEN(B6)>0,MOD(INT(C6/100)+MOD(C6,100)/60-INT(B6/100)-MOD(B6,100)/60+24,24),0)) D7 =DURATION(,,IF(LEN(C7)*LEN(B7)>0,MOD(INT(C7/100)+MOD(C7,100)/60-INT(B7/100)-MOD(B7,100)/60+24,24),0)) D8 =SUM(D)

Notes.

In both tablse, row 1 is header row and row 8 is footer row.

Formulae in Table 1::D2 and Table 2::D2 can be filled down across D2:D7.

Tables are built with Numbers v2.

Good luck,

H

Hi Superdad,

I have long been struggling with Date & Time formats and Duration formats in Numbers. A particular feature (or bug) has been the 'Crossing Midnight' problem when a shift starts on one day and finishes on the next day.

When you enter a Date in a cell, it inserts midnight (0:00) at the beginning of that day as a default Time.

As Barry points out, when you enter a Time in a cell, it carries today's Date as the default.

That is how Numbers works, and is great if an employee enters Start Time on the day when they start a shift, and enters Finish Time on the day when they finish a shift. Then the Duration of work is correct.

Also, the Numbers Date & Time format is good for data-loggers that record measurements at intervals.

I tried this Numbers sheet using the Apple Base Time of midnight at the beginning of 1st January 1904 ('Base Time' table). That is the Apple Base Time because it is the first Leap Year in the 20th Century, and makes it easier to project Leap Years forward. I think of it as the interval from midnight at the beginning of 1st January 1904 until NOW.

The 'Base Time' table has an actual Date & Time in A2 (no formula).

The 'NOW' table is just to keep me on my toes. Formula in A2

=IF(B2,NOW(),NOW())

Click on the Checkbox to update NOW.

That forces an update to NOW.

The 'Time Sheet' table

Column A is formatted as Date & Time (just for illustration).

Formula in B2 (and Fill Down)

=A2−Base Time::$A$2

Formula in D2 (and Fill Down)

=C2−Base Time::$A$2

Formula in E2 (and Fill Down)

=D2−B2

This may not solve your problem, but I had fun playing with the Apple Base Time, and I learnt a lot.

Regards,

Ian.

If you haven't done so already, I recommend having a look at the 'Employee Schedule' (in the Business section of the Template Chooser that appears when you File > New). The formula looks like this:

If you take out the lunch hour adjustment, it's:

=IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0), D2−C2)

If either the cell in column C or the cell in column D is blank, it explicitly assigns a duration of 0. Otherwise it does a simple subtraction, which results in a duration.

That's essentially the same formula that Ian suggested upthread.

SG

Ah. I overlooked you wanted decimal hours instead of duration. Revised tables follow just in case.

Table 1 A1 date A2 2015-07-01 A3 2015-07-02 A4 2015-07-03 A5 2015-07-04 A6 2015-07-05 A7 2015-07-06 A8 B1 in B2 2015-07-21 07:30:00 B3 2015-07-21 08:00:00 B4 2015-07-21 13:00:00 B5 2015-07-21 14:00:00 B6 B7 2015-07-21 07:00:00 B8 C1 out C2 2015-07-21 14:30:00 C3 2015-07-21 17:00:00 C4 2015-07-21 00:15:00 C5 2015-07-21 02:00:00 C6 C7 2015-07-21 12:00:00 C8 D1 hours D2 =IF(LEN(B2)*LEN(C2)>0,MOD(TIMEVALUE(C2)-TIMEVALUE(B2)+1,1),0)*24 D3 =IF(LEN(B3)*LEN(C3)>0,MOD(TIMEVALUE(C3)-TIMEVALUE(B3)+1,1),0)*24 D4 =IF(LEN(B4)*LEN(C4)>0,MOD(TIMEVALUE(C4)-TIMEVALUE(B4)+1,1),0)*24 D5 =IF(LEN(B5)*LEN(C5)>0,MOD(TIMEVALUE(C5)-TIMEVALUE(B5)+1,1),0)*24 D6 =IF(LEN(B6)*LEN(C6)>0,MOD(TIMEVALUE(C6)-TIMEVALUE(B6)+1,1),0)*24 D7 =IF(LEN(B7)*LEN(C7)>0,MOD(TIMEVALUE(C7)-TIMEVALUE(B7)+1,1),0)*24 D8 =SUM(D)

Table 2 A1 date A2 2015-07-01 A3 2015-07-02 A4 2015-07-03 A5 2015-07-04 A6 2015-07-05 A7 2015-07-06 A8 B1 in B2 730 B3 800 B4 1300 B5 1400 B6 B7 700 B8 C1 out C2 1430 C3 1700 C4 15 C5 200 C6 C7 1200 C8 D1 hours D2 =IF(LEN(C2)*LEN(B2)>0,MOD(INT(C2/100)+MOD(C2,100)/60-INT(B2/100)-MOD(B2,100)/60+24,24),0) D3 =IF(LEN(C3)*LEN(B3)>0,MOD(INT(C3/100)+MOD(C3,100)/60-INT(B3/100)-MOD(B3,100)/60+24,24),0) D4 =IF(LEN(C4)*LEN(B4)>0,MOD(INT(C4/100)+MOD(C4,100)/60-INT(B4/100)-MOD(B4,100)/60+24,24),0) D5 =IF(LEN(C5)*LEN(B5)>0,MOD(INT(C5/100)+MOD(C5,100)/60-INT(B5/100)-MOD(B5,100)/60+24,24),0) D6 =IF(LEN(C6)*LEN(B6)>0,MOD(INT(C6/100)+MOD(C6,100)/60-INT(B6/100)-MOD(B6,100)/60+24,24),0) D7 =IF(LEN(C7)*LEN(B7)>0,MOD(INT(C7/100)+MOD(C7,100)/60-INT(B7/100)-MOD(B7,100)/60+24,24),0) D8 =SUM(D)

Regards,

H

Hi 'Dad,

" IF(OR(B6="",C6=""),""),DUR2HOURS(C6−B6). This appears to be exactIy what you suggested. However, I'm now getting the error message "the formula contains a syntax error"."

Close, but not "exactly":

Original is the formula presented in my post above, modified to appear as it would in D6 using the full cell addresses you've used (eg. B6 rather than the B in my example).

Entered is the formula as you entered it. The closing parenthesis shown in bold (look closely—it's a bit hard to spot) is the one that has been moved.

original: IF(OR(B6="",C6=""),"",DUR2HOURS(C6−B6))

entered: IF(OR(B6="",C6=""),""),DUR2HOURS(C6−B6)

Regards,

Barry

Hi Dad and others,

Here is a way to get around the "Crossing Midnight" problem when an employee enters Start and Finish times on the same day (and don't we all 👿).

If the difference between Start and Finish times is negative, add a Duration of "1d" (one day).

Timesheet, using the 24 hour clock:

This is what is hidden behind the curtain:

Formula in E2 (and Fill Down)

=DUR2HOURS(C2−A2)

Formula in F2 (and Fill Down)

=IF(E2<0,C2+"1d"−A2,C2−A2)

Happy Numbering!

Regards,

Ian.

Hi Superdad,

Try this formula in D2 and Fill Down

=IF(OR(B2="",C2=""),"",C2−B2)

If a cell in B or C is blank ("") it inserts blank into D, else it calculates the Duration.

As for displaying "decimal time", see this discussion

Data Format Changing Back To Automatic

Regards,

Ian.

Hiroto wrote:

If user inputs time without date, date part should be assumed to be null or at least some constant value.

But the application behaves differently. The problem would arise when, for instance, you edit the quasi time-only value on a later date and the calculation yields unexpected result. Since you're not showing date part of the date-time value in question, it would be hard to realise the cause of the problem.

Reasonable workarounds would be a) to use TIMEVALUE() function to extract the true time-only value from date-time value or b) to use special time notation, such as HHMM instead of HH:MM, which Numbers does not interpret as date-time value.

Another easy workaround, if you're using Numbers 3 and you have your cells formatted to display time only and you want to work with "time-only" values (i.e. avoid complications from possibly different dates "hidden" in the date-time value), is to select the cells with the times and run the script below. It forces Numbers to coerce the date in each cell to today's date.

SG

tell application "Numbers" to tell document 1 to tell active sheet

set t to first table whose selection range's class is range

repeat with c in t's selection range's cells

set c'svalue to c'sformatted value

end repeat

end tell

On a side note, I am not sure if this applies to you but there is a problem by using this method. This works fine for starting in the AM and finishing in the PM, but doesn't work for any other time format.

It is better to use =IFERROR(MOD(TIMEVALUE(G2)−TIMEVALUE(E2),1)×24,0). This will work for up to 23.75 hours, doesn't matter if you clock-in PM and clock out AM, PM to PM, and AM to AM, and every which way you can think of.

Hi Charles,

In my testing (see table) your first formula is the best of the bunch for working with decimal hours, shorter and more elegant than Barry's!

And your second formula, for those who want to work with times only, up to 24 hours, and want to "hide" the date and not have to worry about being tripped by the "crossing midnight" problem, is simpler than Hiroto's.

For those who want to work with duration and not decimal hours and may need to go beyond a 24-hour period, Ian's formula (the first solution posted in this thread) is tough to beat.

For those who are lazy like me and don't always want to reinvent the wheel, there's also the Apple template, all ready to go in a minute or so.

SG

Yellowbox wrote:

Here is a way to get around the "Crossing Midnight" problem when an employee enters Start and Finish times on the same day (and don't we all 👿).

I've taken your two formulas, consolidated them into one, wrapped them in DUR2HOURS to give decimal hours, and added the result to the table. It's the only solution so far that both addresses the "Crossing Midnight" problem (starting and ending time entered on the same day, whereas ending time is past midnight and therefore on the next day instead of the same day as Numbers assumes) AND handles periods longer than 24 hours correctly if needed.... Plus it spares the user from struggling with MOD and lets Numbers do the modular arithmetic.

H's warning is well taken. Lot more here than meets the eye, especially when the cells are formatted to hide the date.

SG

For hours in decimals just wrap the template formula in DUR2HOURS, like this:

=DUR2HOURS(IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0), D2−C2))

SG