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

Thanks @Genevieve P., that fixed it!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!