Answered

Bug with sending and retrieving dates to and from excel

(0) Share

Report

Posted on by RG-24071244-0

Hello everyone,

There is a bug in how power apps sends dates to excel or how power apps retrieve dates from excel. Not sure which way but let me explain :

I have an excel table with a column named "Échéance" which is date type. I have dates entered manually like "07/05/2026", and some entered automatically by Power Automate using a serial number iirc and which are displayed in the same format (dd/mm/yyyy).

These dates are displayed in a vertical gallery in power apps just fine by just doing ThisItem.Échéance. It works flawlessly.

But when I try to push a date from power apps to excel with :

Patch(

    Tableau1;

    Defaults(Tableau1);

{

        Échéance:Today();

}

);;

It's displayed in excel as : 23/07/2025 22:00:00 (despite being on the 24th so there is a timezone issue but that's not the main problem). 

This would be absolutely fine if when retrieving it in power apps (via ThisItem.Échéance) it didn't throw an error : 

Valeur attendue « 25569.0005308218 » pour être un format « date-heure » RFC 3339 valide. Format(s) ISO 8601 autorisé(s) : 'YYYY-MM-DDThh:mm:ssZ', 'YYYY-MM-DDThh:mm:ss±hh:mm', 'YYYY-MM-DDThh:mm:ss'.

Translated by ChatGPT :  "Expected value '25569.0005308218' to be a valid RFC 3339 'date‑time' format. Allowed ISO 8601 format(s): 'YYYY‑MM‑DDThh:mm:ssZ', 'YYYY‑MM‑DDThh:mm:ss±hh:mm', 'YYYY‑MM‑DDThh:mm:ss'."

So basically dates saved in dd/mm/yyyy in excel works fine, but in dd/mm/yyyy hh:mm:ss break power apps. And for some reason power apps sends the dates in the second format to excel which breaks itself ???

I've tried everything, like sending a serial number (but in excel it turns to 01/01/1970 00:00:46) or trying to convert in power apps afterwards with DateValue and every other existing functions)

I'm pretty sure this is a bug, or at the very least it shouldn't act like that.

Just sending Today() to excel shouldn't break when I just want to display the resulting value in power apps.

Does anyone have some tips with that ?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

RG-24071244-0 13 on at

Like (0)

Report
Copy link

Link copied!

Found a fix :

Use this in the Patch function : Échéance:Int(DateDiff(DateValue("1970-01-01"); Today(); TimeUnit.Days) + (Hour(Now()) + Minute(Now()) / 60 + Second(Now()) / 3600) / 24)*86400000;

Once in excel it will be the right format (dd/mm/yyyy)

Was this reply helpful? Yes No
Suggested answer

pavanmanideep 574 Power Up Champ - 2025 on at

Like (1)

Report
Copy link

Link copied!
Hi,

Please use this

Text(your_date_column, "yyyy/mm/dd")

Syntax: Text( Date/Time Value, Format )

Common format codes:

dd: Day of the month (e.g., 01, 15, 31)

mm: Month number (e.g., 01, 12)

yyyy: Year with century (e.g., 2023)

yy: Year without century (e.g., 23)

hh: Hour in 12-hour format (e.g., 01, 12)

HH: Hour in 24-hour format (e.g., 01, 23)

mm: Minute (e.g., 05, 59)

ss: Second (e.g., 00, 59)

tt: AM/PM (e.g., AM, PM)

ddd: Abbreviated weekday name (e.g., Sun, Mon)

dddd: Full weekday name (e.g., Sunday, Monday)

mmm: Abbreviated month name (e.g., Jan, Feb)

mmmm: Full month name (e.g., January, February)

[$-en-US]: Prefix for English (US) date formats. You can change the locale to other languages and date formats.

Hope this helps...

🏷️ Feel free to @pavanmanideep if you have any further questions or if the issue persists.
✅ Please click Accept as Solution if this helped you resolve your query—it could assist others facing similar challenges.
❤️ Consider giving it a Like if the approach added value in other ways. Your support keeps the community thriving!

Cheers,
PMDY
🏅 Power Apps MVP | Solution Architect

Was this reply helpful? Yes No
Suggested answer

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @RG-24071244-, great for finding the fix, but small suggestion from my side is don't use excel as data source for the app as it locks the excel when multiple app users trying to open the app and there are lot of sync issues.

I would suggest you to migrate any data to SharePoint which 100 times better than excel connector.

And coming to the main question : the problem you are facing is because the power apps accepts the date time as either timestamp or date column which is fine but coming to excel you can't define a column data type to table (which causes lot of issues) my general work around is convert the time to epoch seconds and send it back and retrieve the data in the same format and use epoch conversion logic both sides

Was this reply helpful? Yes No
RG-24071244-0 13 on at

Like (0)

Report
Copy link

Link copied!

Hi @Power Apps 1919

Thank you for your answer, I'm considering migrating to a Sharepoint List, but I need a way to access data very quickly like in excel, and I don't really like how it works with Sharepoint, I'm also wondering if it works well with power automate ?

Also I'm missing a lot of features on Sharepoint that are available on excel, like conditional formatting, any workaround that ? Maybe display the sharepoint list in an excel online ?

Was this reply helpful? Yes No
Suggested answer

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @RG-24071244-0, can you explain this sentence "Also I'm missing a lot of features on Sharepoint that are available on excel, like conditional formatting, any workaround that? Maybe display the sharepoint list in an excel online?"
In power apps we can achieve conditional formatting using Gallery control and can you explain what you are trying to achieve?
or are you saying you want conditional formatting inside the sharepoint list for better representation.
If you want to export the data to excel, we can do that from sharepoint site or Power automate way also.

Update 1: please refer this video, just found: Microsoft Lists: Using formatting and conditional rules
it can help in your use case.

Was this reply helpful? Yes No