Answered

Convert date to serial value

(0) Share

Report

Posted on by iskguy

402

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

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

jsaunders_09 140 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
iskguy 402 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
san4mus 2 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No