Convert calendar date to a serial date

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?

Help

Accepted answers

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

All comments

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

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

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

Quick Links

Tags

This months top contributors

Hollingsworth 335