Subtracting values based on checkbox column

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))

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 01/20/23 Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 01/20/23 Answer ✓

    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
    LFoster ✭✭✭

    Thanks @Genevieve P., that fixed it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!