Subtracting values based on checkbox column

LFoster

I am using a formula to subtract a 30 minute lunch period from the total minutes lapsed based on whether the lunch field has a checkbox or not.

Within the if statement, if the start time hour is lesser than the end time hour the formula works as expected and 30 minutes are subtracted from the total minutes lapsed (criteria 2, value is false).

If the start time hour is greater than the end time hour (criteria 1, value is true) 30 minutes should be subtracted as well. However, what's actually happening is 60 minutes are being subtracted as if both the true and false criteria are being met.

Lunch - checkbox column, 1 = checked 0 = unchecked

Calc Start Time - hour of the start time (i.e. 6 = 6 am/pm)

Calc End Time - hour of the end time (i.e. 6 = 6 am/pm)

=IF(Lunch@row = 1, IF([Calc End Time]@row - [Calc Start Time]@row < 0, (([Calc End Time]@row + 12 - [Calc Start Time]@row) * 60) - 30, ([Calc End Time]@row - [Calc Start Time]@row) * 60) - 30, IF([Calc End Time]@row - [Calc Start Time]@row < 0, ([Calc End Time]@row + 12 - [Calc Start Time]@row) * 60, ([Calc End Time]@row - [Calc Start Time]@row) * 60))

Genevieve P.

Hi @LFoster

It looks like you're just missing an additional parentheses in two places which is causing the confusion for the formula! 🙂

Try this:

=IF(Lunch@row = 1, IF([Calc End Time]@row - [Calc Start Time]@row < 0, (([Calc End Time]@row + 12 - [Calc Start Time]@row) * 60) - 30, (([Calc End Time]@row - [Calc Start Time]@row) * 60) - 30), IF([Calc End Time]@row - [Calc Start Time]@row < 0, ([Calc End Time]@row + 12 - [Calc Start Time]@row) * 60, ([Calc End Time]@row - [Calc Start Time]@row) * 60))

The first addition makes sure that your math is correct (first subtracting, then multiplying, then subtracting again).

The second addition closes off one of your IF statements so it moves on to the next one if Lunch@row = 0.

Let me know if that fixed it for you!

Cheers,

Genevieve

Genevieve P.

Hi @LFoster

It looks like you're just missing an additional parentheses in two places which is causing the confusion for the formula! 🙂

Try this:

=IF(Lunch@row = 1, IF([Calc End Time]@row - [Calc Start Time]@row < 0, (([Calc End Time]@row + 12 - [Calc Start Time]@row) * 60) - 30, (([Calc End Time]@row - [Calc Start Time]@row) * 60) - 30), IF([Calc End Time]@row - [Calc Start Time]@row < 0, ([Calc End Time]@row + 12 - [Calc Start Time]@row) * 60, ([Calc End Time]@row - [Calc Start Time]@row) * 60))

The first addition makes sure that your math is correct (first subtracting, then multiplying, then subtracting again).

The second addition closes off one of your IF statements so it moves on to the next one if Lunch@row = 0.

Let me know if that fixed it for you!

Cheers,

Genevieve

LFoster

Thanks @Genevieve P., that fixed it!