Calculate hours worked

At File > New in your menu you can choose the Employee Schedule template (under Business). Cell G1 in the 'Sign In' table at the bottom gives a formula you can use. You can also explore how the cells are formatted there.

SG

Hi Derek,

Times (ie.time of day values) entered into a Numbers document ALWAYS include a date part as well as a time part. If you enter only a time of day value, Numbers assigns the current date to the date part.

According to your description, each of the work periods entered will begin and end on the same day (ie. NONE will begin before midnight and end after midnight). Provided that is ALWAYS true, AND you always enter the start time during the same calendar day as you enter the end time, you can skip entering the date as part of that time.

Subtracting the start time from the end time will give you a duration value corresponding to the time duration worked in hours and minutes.

DUR2HOURS converts the duration value to a number representing the number of hours, including a decimal part representing the fraction of an hour.

Multiplying by a number representing the hourly pay rate gives the gross pay for that work period.

Times in columns B (start) and C (end) are entered as shown, or as 24 hour clock values (15:00, 16:25 and 13:50 respectively for the 'end' times). Both columns are formatted as Time and Date, with Time set to either the 12 hour clock as shown or the 24 hour clock, and Date set to 'none'.

Column D contains the formula =C-B and is formatted to show duration in the manner shown above.

Column E contains the hourly rate for each worker, formatted as a number.

Column F contains the formula =D*E, formatted as currency, with the default 2 decimal places.

Further details will depend on a more detailed question regarding the results you want.

Regards,

Barry

Hello

I'd use HHMM notation to enter time value and calculate the duration in hours like this:

Table 1 (excerpt) G1 start G2 800 H1 end H2 1345 I1 hours I2 =INT(H2/100)+MOD(H2,100)/60-INT(G2/100)+MOD(G2,100)/60

Note that G2 and H2 are simple integers and there's no separator between HH and MM. This is to prevent Numbers from interpreting HHMM as date-time value, for Numbers can NOT represent time only value in date-time value class. (If you input HH:MM and let Numbers interpret it as date-time value, it yield date-time value of which time is HH:MM:00 and date is the date of the interpretation. For instance, if you find a typo in time value and fix it on a later date, the calculation yields wrong result.)

Sample table is bulit with Numbers v2.

Hope this may help,

H

Hi Hiroto,

One minor revision to your formula:

(Original) I2: =INT(H2/100)+MOD(H2,100)/60-INT(G2/100)+MOD(G2,100)/60

(Revised) J2: =INT(H2/100)+MOD(H2,100)/60-(INT(G2/100)+MOD(G2,100)/60)

Corrects the error shown below. Note the change in Start time and the resulting change in hours (original formula) vs. revised hours (revised formula)

Regards,

Barry

Ahh. Thank you for the correction! I really need reading glasses. 🙂

Table 1 (corrected) G1 start G2 800 H1 end H2 1345 I1 hours I2 =INT(H2/100)+MOD(H2,100)/60-INT(G2/100)-MOD(G2,100)/60

All the best,

Hiroto

I suggest entering the amount of time you worked in duration format. The format is like this:

8h

13h 45m

number can add these correctly

Then you can use the function dur2hours() to convert durations to decimal hours