Numbers time substraction, negative values equals zero

cell J2 formula =IF(OR(D2="",H2=""),DURATION(0),MAX(DURATION(0), D2−H2))

First check is that D2 and H2 both have data. If either is empty then the result is a duration of 0. Without this data check, the formula would be an error triangle in any rows that have missing data.

If it passes that check, the result is the larger of DURATION(0) or the result of the subtraction. DURATION(0) is larger than any negative result from the subtraction.

If you use DURATION(,,,0) instead of DURATION(0) in the formula you will not have to format the column of formulas to be hours and minutes. In an unformatted cell, DURATION(0) will show as 0 weeks while DURATION(,,,0) will show as 0 minutes.

Hi there, use the IF function to do so. For instance, subtracting B1 to A1:

=IF(A1-B1<0,0,A1-B1)

I think you are looking for something like this.

English:

Dutch:

In F2 of my example (your column may be different), filled down:

=ALS(OF(ISLEEG(B2);ISLEEG(C2));"";C2−B2+D2−E2)

If you are worried about a negative 'total hrs' (unlikely unless you have very short shift) then use MAX, something like this:

=ALS(OF(ISLEEG(B2);ISLEEG(C2));"";MAX(C2−B2+D2−E2;"0m"))

For a working example to study, note that the 'Employee Schedule' template at File > New in the menu has a table similar to the one you are constructing:

SG

I believe the issue is in column D. Did you enter the value the same way you did in column H? The former shows the word/abbreviation "uur" while the latter shows "u".

This may indicate that column D is actually a date and not a duration, while column H is a duration.

Both columns should be a duration to perform the arithmetic in the formula given.

Can you post a screenshot of your data (make up data to protect personal information) and the calculation you are doing?