Days to Date Formula

[JonaV]

Hi,

I am trying to figure out a formula to come up with the days to date based on a specific date. For example my data's max date is 10/27/2021, so I want to come up with a formula that brings 300 as the days to date (from Jan 1st to 10/27/2021). Next time the data is refreshed the date will be 11/3/2021, so the days to date should be 307.

Can anyone help with this?

*Also, it would be great to come up with a formula in the most simplified version possible*

Thank you,

[dYoast]

With this formula, I get 299 and 306.

  DateTimeDiff("2021-11-03", "2021-01-01", "days")

You could add 1 to get 300 and 307.

[JonaV]

I want this to be automated. I have the following fields already: Current Year (2021), Current Month #(10), and Current Date (10/27/2021).

I think in order to get 300 we need to have the base year to be 12-31-2020

Again I want this to be automated

[dYoast]

You may have to change the format of Current Date to be an Alteryx date.

Try:

  DateTimeDiff([Current Date], "2020-12-31", "days")

[JonaV]

I have that part, but how do I automate the "2020-12-31" in a couple months it will no longer be that, it will be "2021-12-31". I want full automation. I don't want to have to go into the workflow next year and manually change the 2020 to 2021

[dYoast]

I think I get it now.

Change DateTimeNow in the formula below to Current Date.  Format and set datatype as necessary.

[dYoast · Accepted solution]

Everything in one formula.

[JonaV]

Beautiful!

This will work, but I can see a problem coming soon when the new year arises. I'm using the result of this formula (300) for another formula that calculates weekly annualized attrition.

I'm curious to see what is going to happen if we get to say 01-05-2022 because part of that week will be in the previous year (2021) from 12-29-2021 to 01-05-2022, so in essence this formula I would want it to pull 370 (365 days + 5 days overlap into the next year) to come up with the annualized attrition for the last week of the year. This is the only week out of the year where things will get dicey if I don't find a way to automate it.