convert date to number

Question

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?

Unique ID.xlsx

Claje · Accepted Answer

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