Converting a date into a number (similar to excel)

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

Date Time

Accepted answers

grossal

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

2020-04-17-Converting a date into a number (similar to excel).yxmd

All comments

grossal

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

2020-04-17-Converting a date into a number (similar to excel).yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703