If / then statements with subtraction problem

James Sens

Hello everyone,

I was hoping some of the pro's on here could help me figure something out. I'm creating a hours tracker for employees to use remotely and I need to calculate the overtime hours as anything over 8 or anything over 10 depending on the answer given in a column.

So if [certified] = "Yes" then subtract 8 from [total hours] = OT Hours

if [Certified] = "No" then subtract 10 from [total hours] = OT Hours

It should look like this once done

I was trying to do something like

if ([Certified]@row = "Yes") -8 Value([Total Hours]@Row)

if ([Certified]@Row = "No") -10 Value([Total Hours]@Row)

I'm sure there could be more logical ways of doing this however I'm pretty new and am trying to keep this as simple as possible while I learn. I appreciate any and all feedback.

Thank you!

Mark Cronk

I made a few tweaks - lower case r in Row, removed [ ] around certified since it shouldn't be required. Try this:

=IF(AND(Certified@row = "No", VALUE([Total Hours]@row)>10), VALUE([Total Hours]@row)-10, IF(AND(Certified@row = "Yes", VALUE([Total Hours]@row)>8), VALUE([Total Hours]@row)-8, " "))

Everything looks right based on the screen shot you included.

Any luck?

Mark

Mark Cronk

Hi James,

Try:

=IF(AND([Certified]@row = "Yes", Value([Total Hours]@Row)>8), VALUE([Total Hours]@Row)-8, IF(AND([Certified]@Row = "No", VALUE([Total Hours]@Row)>10, VALUE([Total Hours]@Row)-10, ""))

Mark

James Sens

Hey Mark,

Thanks so much for your help with this. It would've taken me forever to figure out. I'm still getting an unparseable error. I'm going to try walking through the formula you provided after my upcoming meeting to see if I can find what is causing it, any additional input would be great though. Thanks again. -James

Mark Cronk

Excellent. You're missing a paren after the 2nd >10. You need a paren to close the AND function.

Thanks for using the community.

Mark

James Sens

I'm sorry I can't get past this unparseable error. I added the second paren ) behind the 2nd > 10 so the formula now looks like...

=IF(AND([Certified]@row = "Yes", Value([Total Hours]@Row)>8), VALUE([Total Hours]@Row)-8, IF(AND([Certified]@Row = "No", VALUE([Total Hours]@Row)>10), VALUE([Total Hours]@Row)-10, ""))

When I was clicking through the formula it wasn't highlighting the cells referenced so I'm going to try working through this slow using the above formula as reference to try to figure it out. If anyone has any more insight as to where I might be going wrong it would be greatly appreciated. Also Thank you Mark for being so helpful and for welcoming me to this community. :)

Mark Cronk

I made a few tweaks - lower case r in Row, removed [ ] around certified since it shouldn't be required. Try this:

=IF(AND(Certified@row = "No", VALUE([Total Hours]@row)>10), VALUE([Total Hours]@row)-10, IF(AND(Certified@row = "Yes", VALUE([Total Hours]@row)>8), VALUE([Total Hours]@row)-8, " "))

Everything looks right based on the screen shot you included.

Any luck?

Mark

James Sens

Hey Mark,

Thank you so much for your help with this. Looks like that formula did it. I'll have to pay attention to capitalization a bit more. Also why aren't [] required around certified? It's not important just trying to learn as much as I can.

Mark Cronk

Hi James,

Glad you found a solution. Square brackets [ ] are required when the column name has a space or special character in it. Tip: use one word column names to make formula builds easier.

Thank you for contributing to the Community.

Mark