Need help with Hourly Benefits formula

Question

Hello Community!

I'm building a workflow that helps to calculate and report our daily labor costs for various locations. One of the pieces I need to incorporate into my workflow is an "Hourly Benefit Days" field, which calculates a monthly rate of $200,000 divided by the number of days in a month (31 because of October) and then multiplied by the current number of days (15 as of today).

In excel this formula pulls in the proper date numbers from other cells and easily calculates as =200000/31*15. (*Note: the numbers 31 and 15 in this example come from cells such as A2 and B3 so the formula truly is =200000/A2*B3).

I'd like an Alteryx formula that, when I open the workflow daily, automatically takes the current day/month numbers into consideration. I have the following "DateTime" formula strings working separately:

DateTimeFormat(DateTimeLastOfMonth(),"%d") This piece gets me a number of 31 (for October)

DateTimeFormat(DateTimeNow(),"%d") This piece gets me a number for the current day of 15 (for today)

I'm not sure how I can combine these two separate formula strings while also incorporating the $200,000 monthly rate without receiving the following error: "trying to apply a numeric operator to a string value".

If anyone has any suggestions on a formula string that could work, I would greatly appreciate the feedback!

Thanks in advance!

Frank

KP_DML · Accepted Answer

Hello,

Use your functions and convert them to numbers, then you can perform your calculation:

200000 /
(
ToNumber(DateTimeFormat(DateTimeLastOfMonth(),"%d"))
*
ToNumber(DateTimeFormat(DateTimeNow(),"%d"))
)

rfoster7 · Accepted Answer

Also, in case you didn't know, you can reference earlier columns made with a formula tool with formulas lower down the chain. So you could do something like this:

Just be sure you check your data types in your formulas so that you have them in the right numeric format.