making an employee scheduler

you can take a screen shot and use the camera icon in tool bar of this forum to raise a dialog that let's you navigate to, and select the screenshot.

To take a screen shot, type the key combination <COMMAND> + <SHIFT> + 3, the cursort will change to a crosshair, then click and hold one corner, then drag to the opposite corner, then release. The screenshot will be on your desktop shrewdly named something like "Screen Shot 2012-08-21 at 9.20.22 PM.png".

then you subtract (or add) any date/time value to/from another the result is a duration (just as the meaning implies). Comparing durations only make sense if you compare to another duration.

I you compare 1 hour 34 min 46 sec to 7 how do you do that? you cannot... so Numbers complains. 7 has no units and thus is ambiguous.

Post a screenshot with the problem areas and some formulas.

Hi Brian,

A Duration is the result you get when you calculate the difference between two points in Time.

For example, if Joe begins a race at 09:56:07 on August 21, 2012, and finishes the same race at 22:04:59 on August 21, 2012, the Duration of his race is 12h 8m 52s.

Note that the Date is included in specifying the point in time that the race started and the second point in time when it ended.

In Numbers, there is no Time object and there is no Date object. Any cell displaying a date or a time contains a full Date and Time value. Where only a time part has been entered, the date part is set to the date on which the entry was created. here only a date part has been entered, the time part has been set to 00:00:00 (midnight, at the beginning of that day).

For pop-up menus, each 'time' item will include the date on which that item was created. That can, if you haven't set your pop-ups to account for it, lead to 'interesting' results where shifts cross the midnight boundary from one calendar day to the next.

Regarding the error message: Where there's been no start time and no finish time entered, the result of the subtraction will be zero (a number), not 0h (a Duration). The message is that these tow different data types can't be compared, which is likely being attempted in an IF statement in the cell presenting the messaage.

Regards,

Barry

Screenshot 1: Pop-up menu

Although the menu items show only Time of Day, each item's Date and Time value also includes the Date on which that item was created. If any of these items are the original ones that came in the template, that date is July 15, 2008. For any you created, the date is the date on which you created that individual item.

The calculations in the last three columns would indicate that the Date parts of the four distinct values used are all the same date.

Screenshot 2 and 3: Error triangles

The formula shown in the entry box on screenshot 2 is in cell D26 of the main table. The error triangle reports a type-mismatch in the fomula. Here is the source of that error, in the first part of the same fomula, copied from the template:

IF(D$25<Administration Information:: $B$2…

Administrative Information::B2 contains the Duration value 40h 0m

D25 of the main table contains the number value 0

Durations may be compared only with other Durations, hence the error.

The number value arises because no value has been entered in column D. D6, and the rest of the "Hours" cells in column D, contain a trap that places an empty string (a text value) in the cell if either D4 or D5 contain an empty string, and an error trap that places an empty string in the cell if D5-D4-lunch_duration returns an error.

SUM, in D25 treats all of these text entries as having a numeric value of 0 (NOT 0h or 0m, just 0), sums the zeroes and returns the result: 0.

The other error triangles are either the result of the same type mismatch error (Overtime pay row), or arise because the formula in a particular cell references a cell containing an error (Total pay row)

Replacing the formula in D25 with this revised version, then filling it right, should remove the cause of the error triangles. The formula replaces a zero result with the expected 0h 0m Duration value.

D25: =IF(SUM(D6,D9,D12,D15,D18,D21,D24)=0,DURATION(,,0),SUM(D6,D9,D12,D15,D18,D21,D2 4))

Regards,

Barry

Hi Brian,

I have edited the Employee Schedule Template to eliminate the problems with bad work hours due to editing the Pop-Up list. It also puts all Hours calculations in decimal format rather than hours and minutes. (Just my Preference)

If you want to take a look, you can download it here.

You can Copy and Paste your Pop-Ups to my version of the document.

Jerry

Brian,

Glad we are headed in the right direction. By the way, did you see the answer to your question: "Finally, how does one make available the numbers document for you to review? Now that iwork and mobileme are by the way. I don't see how to attach a document to this message."?

So, what kind if fries would you like with that sandwich? I suggest instead of Total Hours we have a breakdown into Straight Time Hours and Overtime Hours, with a grand total if you wish. That way hours could reach the Overtime category two different ways.

Jerry