Convert calendar date to a serial date

[Debster]

I am trying to convert a calendar date to a serial number.  Example:2018-08-11 would be 43323.

The goal is to use this in part of a concatenated field.  Name + Product + Serial Number.

I have Name + Product + Calendar Date, e.g., "Jones Product A  2018-08-11" and want "Jones Product A 43323".

Any suggestions?  

[danilang · Accepted solution]

Hi @Debster

Using the dates in your example, it looks like the start of your calendar epoch,i.e. the day your counting from is 1899-12-30, the same as excel date "0"   Assuming you have [date] as a date field, calculate the day number use this formula in the formula tool

DateTimeDiff(Date,StartofEpoch,"days")

date=the date you're interested in "2018-08-11"

StartofEpoch= your startdate "1899-12-30"

"days" = the period you're interested in , i.e. "years","months","days","minutes", etc

Dan

[Debster]

This works like a charm.  Thank you for making this easy!

Is the "StartofEpoch" the earliest date I would have in my file?

[danilang]

It depends how you want to start counting.  If you want to start from the earliest in your file, then pick that date.  If the data in the file changes though and you get an earlier date, you'll end up with a negative number.  Alteryx will handle the negative number, but it might look messy on your reports.  

Starting with 1899 makes it unlikely that you'll end up with an earlier date, hence no negatives, unless your processing historical, genealogical data or something like earthquake data.  The first recorded earthquake dates back to 1831 BC

All of this makes me wonder why you need to represent the date as a number?  Is it to match something in Excel?  If you just leave the date as a native Alteryx "Date" field, you can add and subtract any interval you want, extract the month, day, year,etc. find the elapsed time between any 2 dates.

Dan

[Debster]

Thank you for the explanation.  Makes perfect sense!

The serial dates are needed because I'm using Alteryx to feed data into BoomiFeeder to go into another system.  (Lots of learning for me using Blob)

The end system requires dates in a serial number format per the business requirements I was given.  My source files used WE calendar dates. My temporary workarounds was pulling in an excel sheet to do the conversion, but I wanted to know how to do it through the Alteryx tool and keep as much in one place, one tool.