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

Convert calendar date to a serial date

Debster
6 - Meteoroid

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?  

 
4 REPLIES 4
danilang
19 - Altair
19 - Altair

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

 

Date number.png

 

Dan

 

Debster
6 - Meteoroid

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

 

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

danilang
19 - Altair
19 - Altair

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
6 - Meteoroid

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.

Labels