Function in numpers that shows a different result depending on the conditions

Hi Giannisgen,

Testing for days off

Formula in D2 (and fill down) is IF(OR(B2="",C2=""),DURATION(,,0,0),C2−B2)

If a cell in column B or C is blank (=""), then insert a Duration of ,,0,0 (no weeks, no days, zero hours, zero minutes)

Else insert C-B.

Formula in E2 (and fill down) is IF(D2≤DURATION(,,0,0),DURATION(,,0),D2−"8h")

Please call back with questions.

Regards,

Ian.

Hi

I am glad it works with semicolon (;) 🙂.

Now to fix those negative Durations when your shift crosses midnight. We need to add one day (+"1d")

Formula in E2 (and fill down) is IF(B2>C2,D2+"1d",D2)

If Start is greater than End, then add one day, else copy the value in D.

Formula in F2 is IF(E2>DURATION(0,0,8,0),E2−"8h",DURATION(0,days,0,0))

You can hide Column D and rename Column E.

Regards,

Ian.

In this other cell where you calculate the overtime, use the function IF.

For instance, if your calculation is (A1-8) where cell A1 is where you enter the work hours, the function should be:

IF(A1-8<0,0,A1-8)

So if (A1-8) is lower than 0, 0 will be returned, otherwise the calculation (A1-8) will be returned.

Hi Giannisgen,

It works here.

One difference I can see is that your times have leading zeroes (08:00) but I can't format for that. Mine are 8:00.

What happens if you format as 12 hour clock?

Another little problem: your shifts on Saturday and Sunday "cross midnight" and give a negative duration. We can fix that, but first let's get your table working.

Regards,

Ian.

Hi Giannisgen,

Custom Units may work for you.

Regards,

Ian.

Hi Giannisgen,

Just a thought. If your region uses comma (,) as the decimal separator, use semicolon (;) in formulas.

What is your Language and Region?

Regards,

Ian.