cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MadhuVadlamani
Helper I
Helper I

Date column in excel sheet data source stored as fraction needs to be converted to date in Powerapp

I have an excel sheet as a data source.

There are 2 date columns in it. Currently they are in fraction format.

They are displayed as fractions in Powerapps.

I am looking to get them to display as Shortdate or Longdate

Can you please help?

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

By fractions I assume you mean they come in as a whole number, like 43241.75.  The main number portion of that number is the number of days after 12/30/1899.  Its supposed to be 1/1/1900 but its off by two since 1/1/1900 would be 1 and 1900 didn't have a Leap day.  The decimal portion represents the fractional part of a a day (Time).  So the formula to translate that in Power apps would be

Text(
    DateAdd(
        DateTimeValue("1899-12-30 12:00:00 AM"),
        RoundDown(
            (Value(DateFieldValue) * 86400),
            0
        ),
        TimeUnit.Seconds
    ),
    ShortDateTime
)


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

11 REPLIES 11
Pstork1
Dual Super User III
Dual Super User III

By fractions I assume you mean they come in as a whole number, like 43241.75.  The main number portion of that number is the number of days after 12/30/1899.  Its supposed to be 1/1/1900 but its off by two since 1/1/1900 would be 1 and 1900 didn't have a Leap day.  The decimal portion represents the fractional part of a a day (Time).  So the formula to translate that in Power apps would be

Text(
    DateAdd(
        DateTimeValue("1899-12-30 12:00:00 AM"),
        RoundDown(
            (Value(DateFieldValue) * 86400),
            0
        ),
        TimeUnit.Seconds
    ),
    ShortDateTime
)


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

MadhuVadlamani
Helper I
Helper I

Hello PStork,

Thanks for the response. I am looking to get only the date, do not need the time. Will this formula still work?

Also,

For Value(DateValueField)* 86400 -> What should the DateValuefield format be? -> Thisitem. nameofdatecolumn?? or Table.Nameofdatecolumn

Please excuse my ignorance, thank you

MadhuVadlamani
Helper I
Helper I

Thanks a lot PStork, this worked for me.

For just the date change the format you are using.  So ShortDate instead of ShortDateTime. Otherwise the formula is the same.  DateValueField is whatever the date field is coming from excel.  The formula you provided won't change it to a Date.  It will still be a whole number.

Text(
    DateAdd(
        DateTimeValue("1899-12-30 12:00:00 AM"),
        RoundDown(
            (Value(DateFieldValue) * 86400),
            0
        ),
        TimeUnit.Seconds
    ),
    ShortDate
)

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
MadhuVadlamani
Helper I
Helper I

Hello PSTork,

I am facing another issue, if the cell in the excel is blank, this function displays 12/30/1899, how do I get to display nothing instead of this date?

Check if the cell is blank before running it through the formula.  If the cell is blank it will default to 0 which will become 12/30/1899.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Thank you.

I have another date field in the excel sheet that doesnot seem to get pulled into Powerapps. Could you be able to suggest any known issues?

This question is out of context, how do I add multiple fields to a gallery list??

Thanks in advance.

The fields that show in the Gallery are based on what you define as controls in the Gallery template.  By default there are three.  But you can click on the little pencil in a circle icon to edit the template and add more controls.  They can then be adjusted to show whatever field you like.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

There is a date column in my excel sheet(data source) that has the format of shordate. Powerapps is not able to display the dates from this collumn. If I manually change it to fraction/text and apply the formula you mentioned earlier, the dates appear. I want it to display the shortdate as it is. Could you help?

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (981)