User profile for user: amanimf
amanimf Author
User level: Level 1
8 points

pay schedules

making schedules in the numbers App.

Currently i am using the formula below to get a total hrs worked per day and subtracting a 30minute break when hours worked is over 5hrs

C=End time

B=Start time

C−B+IF(C−B>DURATION(,,5),−DURATION(,,,30))


The problem i am having is when the hrs worked is under 5 hrs the answer shows as a number rather than duration of time. (it automatically removes duration) says the formula uses a boolean in place of a number. When hrs worked is over 5 hrs it subtracts 30 minutes but shows total hrs worked as duration not a number.


because some numbers are duration of time and some are regular numbers, when i try to total the hrs per week it obviously gives me an error


Furthermore i need it to give me a total hrs per day if 8 hrs or less and in a different cell give me the total of any hrs worked over 8hrs.

example

If employee #1 worked 8hr + 8hrs + 9hrs


I need the results to show

regular hrs

24 hrs


Overtime hrs

1hr


hope someone can help?



MacBook Air

Posted on May 31, 2020 8:59 AM

Reply
Question marked as ⚠️ Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,993 points

Posted on May 31, 2020 1:35 PM

Syntax for IF: IF(expression,if-true,if-false)


Mapping that syntax onto your IF statement:


IF(C−B>DURATION(,,5),−DURATION(,,,30) )

IF(expression                   ,if-true    ,if-false)


When C-B (a duration) is greater than 5h, expression returns TRUE, and IF returns negative 30m.

When C-B is less than or equal to 5h, expression returns FALSE, and, since you have not specified what to do if-false, IF returns the boolean value FALSE.


Here's a rewrite of your formula, and two to split the result into regular and OT hours:

Formulas:


D2: IF(C2−B2>DURATION(,days,5),C2−B2−"30m",C2−B2)


E2: MIN(D2,"8h")


F2: MAX("0h",D2−"8h")



Row 9 in a Footer Row.


E9: SUM(E)


F9: SUM(F)



Regards,

Barry

View in context

Similar questions

3 replies
Sort By: 
Question marked as ⚠️ Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,993 points

May 31, 2020 1:35 PM in response to amanimf

Syntax for IF: IF(expression,if-true,if-false)


Mapping that syntax onto your IF statement:


IF(C−B>DURATION(,,5),−DURATION(,,,30) )

IF(expression                   ,if-true    ,if-false)


When C-B (a duration) is greater than 5h, expression returns TRUE, and IF returns negative 30m.

When C-B is less than or equal to 5h, expression returns FALSE, and, since you have not specified what to do if-false, IF returns the boolean value FALSE.


Here's a rewrite of your formula, and two to split the result into regular and OT hours:

Formulas:


D2: IF(C2−B2>DURATION(,days,5),C2−B2−"30m",C2−B2)


E2: MIN(D2,"8h")


F2: MAX("0h",D2−"8h")



Row 9 in a Footer Row.


E9: SUM(E)


F9: SUM(F)



Regards,

Barry

Reply
User profile for user: amanimf
amanimf Author
User level: Level 1
8 points

Jun 1, 2020 8:28 PM in response to Barry

Thanks Barry. I copied and Typed Formula out exactly and the results are in duration but the 30minute break isn't being subtracted when over 5 hrs


it gives me a snytax error when I write your formula in. It does the math only when i change the "30m" to duration,,,30) but the break is not subtracted.

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

pay schedules

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up