Question

Adding another element to a working formula

Forms

Updated June 17, 2022

asked on June 17, 2022 • Show version history

I am using this formula to calculate hours worked:

=IF((AND(DateBegan<>"",DateEnded<>"")),ROUND(MULT((SUB((ADD(DATEVALUE(DateEnded),TIMEVALUE(DateEnded))),(ADD(DATEVALUE(DateBegan),TIMEVALUE(DateBegan))))),24),2),"")

The customer would like to add another element, namely the ability to deduct a 30 or 60 minute lunch break. I created radio buttons for the lunch choices, then a hidden field to show either 0, 0.50, or 1.00:

Then I very inelegantly tried to tack on the subtraction of the Lunch_Time variable to the formula:

=IF((AND(DateBegan<>"",DateEnded<>"")),ROUND(MULT((SUB((ADD(DATEVALUE(DateEnded),TIMEVALUE(DateEnded))),(ADD(DATEVALUE(DateBegan),TIMEVALUE(DateBegan))))),24),2),"")-Lunch_Time

When entering the date/time info, I get a calculation error UNTIL I enter the time ended, then the error goes away and everything works.

How can I fix the formula to get rid of the error?

Thank you.

0 0

Answer

SELECTED ANSWER

replied on June 17, 2022

I didn't set-up a test to try it, but perhaps this change will work:

=IF((AND(DateBegan<>"",DateEnded<>"")),ROUND(MULT((SUB((ADD(DATEVALUE(DateEnded),TIMEVALUE(DateEnded))),(ADD(DATEVALUE(DateBegan),TIMEVALUE(DateBegan))))),24),2)-Lunch_Time,"")

I moved the -Lunch_time into the IF statement, at the end of the actual calculation. Where it was outside it was trying to take an empty string and subtract a number from it.

P.S. - Please consider editing your post to change it to a question instead of a discussion - that way you can mark it as answered once your question is resolved.

0 0

Replies

replied on June 17, 2022

Thank you, Matthew, that worked! I did change my post; I apologize, I wasn't paying attention.

1 0

replied on June 17, 2022

Fantastic! I'm happy that worked.

0 0

You are not allowed to follow up in this post.

Question

Question

Adding another element to a working formula

Answer

Replies

Sign in to reply to this post.