I see many answers to convert a date serial number (like out of excel) to a date, but what if I want to reverse that? What I am trying to do is create a calculated column with a person's last name and DOB value. So if I have Allison and 9/6/67, it would be ALLISON24721.
Thanks for the information
Just to make a correction here, +2 is added not because both 1/1/1900 and the end date are inclusive (in that case 1 should have been added as we are taking difference), +2 is added as MS Excel treats 1900 as leap year which is exception! Check this link - https://learn.microsoft.com/en-us/office/troubleshoot/excel/determine-a-leap-year.
For example, if you test using leap year formula below, 1900 is NOT a leap year.
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")
But when you use DATEVALUE function below, it allows 29 days in Feb for the year 1900 and that causes extra 1 day (along with 1 day for 01/01/1900) to be added to make it consistent with MS Excel Serial Date value.
=DATEVALUE("02/29/1900")
Hope that helps.
Thank you! And yes, I can also use the yyyymmdd format which is actually what I ended up doing. But it is also good to know this formula for the serial date
I think it would be more common to use "yyyymmdd" format but if you want to use serial the below formula will give it to you.
DateDiff(Date(1900,01,01),DOB value + 2)
Side note Serial date is days since 1/1/1900, including 1/1/1900 and the end date, this is what the +2 is for.