How to sum hours if they are after certain hour

luq45 wrote:

What I'm trying to do is to use a conditional so Numbers will count all my hours worked after 10PM and add them up to multiply by the value, so I can then add it to my base salary. 

You can count the hours After 22:00 with something like this:

In E2, filled or copied down the column:

=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",DUR2HOURS(MOD(C2−"22:00",DURATION(0,0,24))))

In D2, filled or copied down:

=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",DUR2HOURS(MOD(C2−B2,DURATION(0,0,24))))

The IF and OR are just testing for blanks so you don't get a warning triangle for a day like Wednesday, where you haven't entered hours.

The heart of the formulas is:

=DUR2HOURS(MOD(C2−"22:00",DURATION(0,0,24)))

=DUR2HOURS(MOD(C2−B2,DURATION(0,0,24)))

The MOD applies the 24 hour clock so you can enter 00:00 when checking out at midnight and not get a negative number.

The DURATION(0,0,24) simply means 24 hours. In some regions you could use "24h" in the formula instead to make it shorter, e.g.:

=DUR2HOURS(MOD(C2−"22:00","24h"))

DUR2HOURS converts to decimal hours for use in further calculations to derive your pay.

Your "Regular" or "Day" hours are simply the Total minus the After 10:00, or =D$10−E$10 in this example.

Be sure to substitute ; for , in the formulas if your region uses , as a decimal separator.

SG