Converting a date into a number (similar to excel)

[kyiu]

I currently have a list of dates 1/1/2020, 1/2/2020, 1/3/2020....etc listed in excel. However, I want to turn those dates into a number just like how excel does it when I change the format of 'Date' to 'General'. Is there a way to dynamically change these dates to a string of numbers in Alteryx? There will be a lot of numbers, so I would like to build a formula to change all at the same time.

Example:

Original Date	Target Number (Date as a number in excel)
1/1/2020	43831
1/2/2020	43832
1/3/2020	43833
1/4/2020	43834
1/5/2020	43835
1/6/2020	43836
1/7/2020	43837
1/8/2020	43838
1/9/2020	43839

[grossal · Accepted solution]

Hi @kyiu,

here is a quick way to do this:

Output:

Formula:

DateTimeDiff([Date],'1900-01-01','days')+2

Workflow attached. Let me know if it works for you.

Best

Alex

[kyiu]

@grossal

Thanks, it works!

[nickgriff01]

Hi.  I came up with the exact same solution through some trail and error but i can't for the life of me understand why it's a "+2" at the end not a "+1".  Can you explain this please?!  It's driving me nuts!

Thanks

Nick

[grossal]

Hi @nickgriff01,

I'd like to cite @JordanB from this post: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-convert-the-Excel-numerical-dat...

---

You need to minus 2 off the number provided by Excel, due to the issue with 29th Feb 1900 and the inclusiveness of the start/end dates. (1900-01-01 is day 1 not day 0, and 1900 was not a leap year but excel calculates it wrongly).

---

We are doing it the other way around, therefore it's +2 instead of -2.

Best

Alex