convert date to number

[JonathanRichey]

What would be the best way to convert a date to a number? My end goal is to use the date and another field as a unique identifier. For example in the excel workbook provided the unique id is concatenated from the Report Date and EENum. What is the best way to create a unique id with a date and a number?

[Claje · Accepted solution]

It depends on if you want the number to be discernible as a Date.

A really quick example might be something like :

DATETIMEFORMAT([datefield],'%Y%m%d')+TOSTRING([numberIDField])

If you want to use the Excel numbering format, you would want to do something like:

TOSTRING(DATETIMEDIFF([datefield],'1900-01-01','days'))+TOSTRING([numberIDField])

Make sure to replace [datefield] with your date field, and [numberIDField] with your other field

[phoenix64]

Hi Claje,

I am using the second out of the two formats you shared:

TOSTRING([numberIDField]) + TOSTRING(DATETIMEDIFF([datefield],'1900-01-01','days'))

but I am seeing different date then what we normally get in excel.

Example - 

NumberField - 8    and DateField - 2010-04-04 

Excel Syntax = NumberField & DateField

>> Excel Output - 840272

Alteryx Syntax = TOSTRING([NumberField]) + TOSTRING(DATETIMEDIFF([DateField],'1900-01-01','days')) 

>> Alteryx Output - 840270

What could be the reason?

Thanks!