Problem updating dates from my power apps to my Excel file

Like (0) Share

Report

Posted on 29 Oct 2024 09:44:49 by Charline_74

I use a power app to update a column called End Date in Excel.
However, when I update the date, for example to 09/09/2024 in my Excel file, I get 9/8/2024 10:00:00 PM
How do I get the date right without the time?

Here are the details of my app (important information, there are people in France and India who use this app) :

End Date_DataCard2 :

Default : DateAdd(ThisItem.'End Date';TimeZoneOffset();TimeUnit.Minutes)

Update : DateAdd(DateValue8.SelectedDate;-TimeZoneOffset();TimeUnit.Minutes)

DateValue8 :

DateTimezone = DateTimeZone.Local

DefaultDate = Parent.Default

Format = DateTimeFormat.ShortDate

Categories:

Using Apps Using Formulas

General Questions

All responses (7)

Answers (0)

ronaldwalcott 3,596 on 07 Nov 2024 at 01:50:38

Like (0)

Report

Problem updating dates from my power apps to my Excel file

Sorry, I see one major design issue.

When managing applications used in different time zones you should not store pure date values and try to convert them between time zones.

You are either using time zones or you are not, thus storing datetime values not date. There is no function to convert a date between time zones as time zones are essentially a function of time that may cross dates,

If you are using only date, then you have to consider the meaning of the date. Say work periods, I start work at this date and end at this other date. Time zone bears no importance to the number of days that I worked and may not even be important to someone working in Australia which would be one day ahead of me using the app in Barbados.

How are you using the end date that you need it stored as a date rather than datetime. Any reporting system using the data would be able to use only the date component if required.

Just my opinion, I could be wrong.
Charline_74 42 on 06 Nov 2024 at 08:09:02

Like (0)

Report

Problem updating dates from my power apps to my Excel file

Hi ronaldwalcott

I'm trying to update the date according to the local time zone
ronaldwalcott 3,596 on 05 Nov 2024 at 19:19:39

Like (0)

Report

Problem updating dates from my power apps to my Excel file

You are adding minutes to a date field obviously defined as datetime, it will display as datetime.

Are you trying to update the date based on UTC time so are you trying to make adjustments based on the local time zone?
Charline_74 42 on 05 Nov 2024 at 10:49:45

Like (0)

Report

Problem updating dates from my power apps to my Excel file

Thank you for your feedback. I've tried both solutions and neither works.

Tn reply to @ronaldwalcott
yes I just want to update a date with no time component
Suggested answer

Kellboy2243 51 on 31 Oct 2024 at 00:09:27

Like (0)

Report
Problem updating dates from my power apps to my Excel file

To address the issue of correctly updating dates from Power Apps to an Excel file without affecting the time component, you need to ensure that the date format and time zones are handled properly. Here’s a detailed solution based on the scenario you provided:

Problem Analysis

Date Format Mismatch: When updating a date in Excel, the date might be interpreted differently due to time zone differences or date format mismatches.

Time Zone Adjustment: The code you provided includes adjustments for the time zone offset which might be causing the date to appear incorrectly.

Solution Steps

Remove Time Component: Ensure the date being written to Excel only contains the date part and not the time.

Format Date Correctly: Ensure the date is formatted correctly before it is updated to Excel.

Implementation

Remove Time Component from Date:

Use the DateValue function to strip the time component and keep only the date.

Update Formula:

Adjust your Update formula to ensure only the date part is passed.

Here’s how you can modify your current approach:

Adjusted Code

// Default value for the data card Default: DateValue(ThisItem.'End Date') // Update value with only date part, removing time component Update: DateValue(DateValue8.SelectedDate)

Notes on Time Zone

If time zone differences still affect the date, ensure that the DateValue function correctly interprets the local time zone of the user. In your current configuration, you used TimeZoneOffset which adjusts for local time differences. Here, stripping the time component using DateValue should inherently address the time zone difference since it only works with the date part.
ronaldwalcott 3,596 on 30 Oct 2024 at 23:13:46

Like (0)

Report

Problem updating dates from my power apps to my Excel file

Can you explain further what you mean by this?

Do you want to use dates without the time component?

Do you want to update the date only by days and not time?

However, when I update the date, for example to 09/09/2024 in my Excel file, I get 9/8/2024 10:00:00 PM
How do I get the date right without the time?
mmbr1606 12,121 Super User 2025 Season 1 on 30 Oct 2024 at 14:30:28

Like (0)

Report
Problem updating dates from my power apps to my Excel file

hey

can u trythis approach:

update property for end date_datacard2:

Text(DateAdd(DateValue8.SelectedDate, -TimeZoneOffset(), TimeUnit.Minutes), "[$-en-US]yyyy-mm-dd")

default property of the same control:

Text(DateAdd(DateValue8.SelectedDate, -TimeZoneOffset(), TimeUnit.Minutes), "[$-fr-FR]dd/mm/yyyy")

if it helped please mark as verified answer,

cheers