Error fields in Numbers timesheet / pay calculation table due to blank future hours fields.

Aliveinabrokenworld wrote:

I now have zeros instead of error messages in future fields under Hours and Total $. Is there a way to make them stay blank without error fields? Just seems messy and dumb.

I tend to leave formulas simpler so they show error triangles with sometimes useful error messages (which appear when you click on them), and then just don't keep blank rows in the bottom of the table. However, if you want a neater appearance while still having blank rows at the bottom then you can do something like this:

In E2, filled down:

=IF(OR(ISBLANK(B2),ISBLANK(C2)),"", MOD(C2−B2,"1d")−D2)

In F2, filled down:

=IF(ISNUMBER(E2),DUR2HOURS(E2)*Rate::$A$2,"")

This is based directly on the built-in 'Employee Schedule' and took about 10 minutes to modify.

Because of the MOD it doesn't care what dates are in your In and Out columns and gives the expected result if you start a shift in the evening and end it past midnight. In fact you could just format your In and Out columns as Text, enter "just the times", and Numbers will still happily do the time calculations for you

SG

Alive,

This solution is in my style. It may not suit you, but this is where I've landed after a few years of using spreadsheets.

I like to use a Constants Table. I simplifies some expressions and makes the sheet easier to maintain if, say, the hourly rate changes.

Here are the expressions, as found in Row 2:

Duration: =IFERROR(IF((C2−B2−D2)<Constants::A$2,C2−B2−D2+Constants::B$2, C2−B2−D$2), "")

Hours: =IFERROR(DUR2HOURS(E2), "")

Pay: =IFERROR(F2×Constants::$C$2, "")

You can "Hide" the Duration field, or the Hours field, or leave them both there. If you leave Duration visible, you may want to rename it.

If you look past the IFERROR function that is only there for the case of empty entry cells, it's a pretty simple set of expressions. The Duration expression is only as complicated as it is to accommodate hours that span Midnight. It might not be necessary to test for the negative result in your case.

I hope seeing this solution style is helpful.

Jerry

Your solution changes the duration D3-C3 to a decimal value then back to a duration. That isn't necessary. An alternate solution that also deals with workshifts that span midnight and handles time entries that are made/edited on later dates (and therefore have the wrong date associated with the time),

IF(OR(C3="",D3=""),DURATION(0),MOD(D3−C3,DURATION(,1)))

Format as duration with units of hours and minutes.

Late to the party, but the built-in templates at File > New in the menu are instructive, often deceptively simple and elegant, and can save hours of "reinventing the wheel."

Here's a screenshot of the template called 'Employee Schedule'.

Remove the Employee column and you've got a working document in just a few minutes.

The "crossing midnight" problem, if you have it, is easily addressed with MOD, as Badunit explains.

SG

Hi Aliveinabrokenworld,

To answer this question,

Aliveinabrokenworld wrote:

However, now I can't seem to figure out how to integrate that into this original formula I was using to solve the problem of anomalous shifts whose end times were < start times (like overnight shifts:)

IF(StartTime>EndTime,EndTime+1,EndTime)-StartTime

Stringing the second IF clause after the end of the first formula did not seem to work, and I tried many other variations in attempts to combine them.

Welcome to the "Crossing Midnight" problem!

You are on the right track.

This may help. I added extra columns (C and D) to display the full Date & Time.

I prefer the "h m" display for Duration formats and "am/pm" for time of day. That way my old brain does not confuse the two.

Times entered on the actual Start and Finish days work as expected.

Times entered on the same day require +"1d" to correct the "Crossing Midnight" problem.

Formula in E2 is B2−A2

Easy, but could be wrong!

Formula in F2 is IF(B2<A2,B2−A2+"1d",B2−A2)

To catch and correct "Crossing Midnight".

Regards,

Ian.

Hi Aliveinabrokenworld,

My reply was to demonstrate the way that Numbers deals with Date & Time format. If you enter a time of day without a date, Numbers automatically adds the date when the entry was made.

Therefore, if Start and End times are entered on the actual days of Start and End, the dates will be correct and the Duration will be correct, even if the shift crosses midnight. However, if the Start and End times are both entered at the end of the shift, there will be a day missing. Thus the need for conditional +"1d" in my second formula.

It took me some time to get my head around the way that Numbers deals with Date & Time versus Duration. Not easy for someone coming from Excel!

BTW, If you enter a Date without a Time, Numbers automatically adds the Time as midnight.

Regards,

Ian.

Alive,

If you choose to use the Employee Schedule Template, note that it deducts a constant amount of time for "Lunch Break" every day, whereas you had a column for break time in your entry table. Also note that the hourly rate is variable by employee and is located in the summary table.

Regards,

Jerry

Hi Julia,

I see replies with several different approaches, so I won't stir the pot. I will confine my reply to this question:

Aliveinabrokenworld wrote:

Hi Again Ian,

Right, it did take me a minute to see that, I wish there was a day to manually change the date but only have time showing.

Thanks again,

Julia

Yes, indeed. A cell formatted as Date & Time to show only the Time still contains the Date.

For example cell C5 where the shift has "crossed midnight".

Click twice on that cell. That cell will display the full contents of that cell and will place the cursor in the "text layer" of that cell. Now ready to edit the Day part of the Date.

However, in my Column E, the formula for Hours has already compensated by adding 1 Day if necessary, so there is no need to edit the times.

Regards,

Ian.