Excel Date to Sharepoint List then Show in powerapps

(0) Share

Report

Posted on by MJ_RT

Hello,

I have created a flow to import data from Excel to SharePoint List. this includes a date.

I have tried:

Excel field farmated as Date AND SP List formated as Date ==> Failed

Excel field farmated as General AND SP List formated as Date ==> Failed
Excel field farmated as Date AND SP List formated as Text ==> Succeed

Excel field farmated as Text AND SP List formated as Text ==> Succeed

The date now on SP List shows numbers in the format 44276 (field is a text field).
when I convert this to date using Datevalue or Text(datevalue the PowerApps lable shows wrong day and month and even the year is 44xx.

I would appreciate any help to transfer or convert it correctly into German date format (only date) dd.mm.yyyy

Regards,

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

Pstork1 69,661 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Excel stores dates as the number of days since 01/01/1900. The whole number portion is the actual number of days and the decimal portion is the fractional number of days. so the calculation to convert it to a date looks like this.

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

You use 12/30/1899 because 1900 didn't have a leap day 2/29 and the count starts with 1/1 as the first day. So math is off by 2 if you use 1/1/1900 .

If your locale doesn't put it in German format automatically then you can force it with

Text(DateValue, ShortDateTime,"de-DE")
MJ_RT 69 on at

Like
a
(0)

Report
Copy link

Link copied!

thanks, but where should this code go?

DateAdd(DateTimeValue("1899-12-30 12:00:00 AM"),RoundDown(( Value(ExcelDate)* 86400),0), TimeUnit.Seconds)
Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

When you are displaying the date from Excel or saving it to SP. This will translate the serial date from Excel to an actual Date Time value.