Convert Date/Time Field Format to a Sequence ID

[RifferX]

For pHI purposes, need to convert a Procdate column into a large number based SequenceID. 

Date/time format: 2014-04-06 02:02:28.000

Desired Output: 12,439,369 (something similar -- basically to trim the date/time down to a day with each day having its own SequenceID)

In excel, I used different formula combos in separate steps. I am not sure what approach to take with Alteryx and was looking for some ideas. There are going to be a few million rows by calendarweek so in essence each time I run this process there will only be 7 new SequenceIDs since I am creating a new, aggregated table on a weekly basis.

[KaneG · Accepted solution]

You can just take the number of days from an arbitrary point...

DateTimediff(Left([Date],10),'2000-01-01','days')

Kane

[MSalvage · Accepted solution]

@RifferX,

Not totally sure if this gets at exactly what you want, but it might help you down the right path(attached).

Best,

MSalvage

EDIT: The actual math in my sample workflow will not necessarily be a unique ID, but might help you use your formulas.

Ex: (2014yr)*365 + (01m)*30 + (31d) = 735171

and (2014yr)*365 + (02m)*30 + (01d) = 735171

[RifferX]

Thank you sir. (KaneG) Had to jump on at home to try it out and it took a couple hours to run. Works well although I may have to change the default date and add some extra steps to provide more security. Appreciate your time and seems like a great solution. 

[RifferX]

MSalvage, this is a viable solution and provides a different angle of attack than KaneG's. Some extra steps but nothing that would be a deterrent. Definitely appreciate your time and effort in helping me out and this will also provide a nice little workflow reference. Have a great Thanksgiving!