Determining new date based on Date +/- a number

Hello-

I'm looking for help turning something I've done into excel into a formula tool. In the data below, I'm trying to make a new field called 'new CSD/BSD'. In excel, this is done with '=C3-G3' or subtracting the Days of Service from the Hire Date, and then reformatting that number as a date. I can't seem to find the right formula.

Service Date Capture.PNG

Alteryx service date example.xlsx

Transformation

Accepted answers

DataNath

Ah yep that's it @katherinetdavita! At the moment, as the field is stored as a string, this is essentially the same as telling Alteryx to take away 'hello'! You're creating the field in the Formula tool and the default for new fields you make is a V_WString so this is quite a common thing to run into! Luckily there's 2 very simple ways of handling this.

1) Where you're making this [NEW Days of Service] field, just change the data type of it here to something numeric (Double, Int32 etc):

Days since Term.PNG

2) If you don't want to change the data type and do want this field stored as a string, temporarily convert it within the [New CSD/BSD] expression, like so:

DateTimeAdd([Hire Date],-ToNumber([NEW Days of Service]),'day')

Hope this helps!

All comments

DataNath

Hey @katherinetdavita, for this we'd just use the DateTimeAdd() function. Make sure you put a - in front of the interval as you're taking days away!

Take days of service from hire date.yxmd

katherinetdavita

@DataNath makes sense, but what's going on with the word "add" in the second row of the formula. I'm getting a parse error when I include that and also (if it's there in error) when I exclude it.

Service Date Capture2.PNG

DataNath

Oh sorry @katherinetdavita - just delete that! I must've left it in there by accident. The expression should just be the first line

DateTimeAdd([Hire Date],-[Days of Service],'day')

If you're still getting the error when you remove the 'add' on the second line, I think the most likely issue is that your [Days of Service] field is a string - just check the data type as it should be numeric. If not, can you post the error to help troubleshoot?

katherinetdavita

@DataNath I think you're right about the String. Here's the formula where the workflow calcs the Days of Service:

Days since Term.PNG

And here's the error after fixing the expression:

DataNath

1) Where you're making this [NEW Days of Service] field, just change the data type of it here to something numeric (Double, Int32 etc):

Days since Term.PNG

2) If you don't want to change the data type and do want this field stored as a string, temporarily convert it within the [New CSD/BSD] expression, like so:

DateTimeAdd([Hire Date],-ToNumber([NEW Days of Service]),'day')

Hope this helps!

katherinetdavita

Thanks @DataNath ! That took care of it! Will Accept as Solution now!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703