Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Convert date to serial value

(0) ShareShare
ReportReport
Posted on by 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:
  • san4mus Profile Picture
    san4mus 2 on at
    Re: Convert date to serial value

    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. 

  • iskguy Profile Picture
    iskguy 402 on at
    Re: Convert date to serial value

    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 

  • Verified answer
    jsaunders_09 Profile Picture
    jsaunders_09 140 on at
    Re: Convert date to serial value

    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)

     

    jsaunders_09_0-1671551289766.png

     

    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.

     

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard