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

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thanks @Genevieve P., that fixed it!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!