Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting a date into a number (similar to excel)

kyiu
7 - Meteor

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 DateTarget Number (Date as a number in excel)
1/1/202043831
1/2/202043832
1/3/202043833
1/4/202043834
1/5/202043835
1/6/202043836
1/7/202043837
1/8/202043838
1/9/202043839
4 REPLIES 4
grossal
15 - Aurora
15 - Aurora

Hi @kyiu,

 

here is a quick way to do this:

 

grossal_0-1587146160786.png

 

Output:

grossal_1-1587146181149.png

 

 

 

Formula:

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

 

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

 

 

Best

Alex

 

kyiu
7 - Meteor

@grossal

 

Thanks, it works!

nickgriff01
5 - Atom

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
15 - Aurora
15 - Aurora

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

Labels