Dates behaving badly...

(0) Share

Report

Posted on by richmus

Hi All,

I've been fighting with this for a few hours but can't work out what I've done wrong...

We've got a very simple canvas app that just collects data and puts it into a table on a OneDrive Excel file.

4 date/time inputs work normally (and their default date is set to be this DateValue), but the first Date input is recording very oddly:

On the excel file, it records every date as the day before, and adds 23:00 as a time to it, even though I have removed the time control and changed the DataCard Update to DateValue3.SelectedDate

Here's the excel output:

I've tried changing the Date Card Update function and the Date Picker Format to: Text(DateValue4.SelectedDate,"dd/mm/yyyy")

but that didn't make a difference to what was recorded.

I've tried deleting and re-adding the data card, and still nothing.

Any ideas?

Thanks in advance,

Rich

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Pstork1 69,599 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

What time zone are you located in? Check to see whether your date pickers are set for local or UTC. I suspect you are in a time zone that is one hour different from UTC and the date picker is reporting the date in local. So when it is saved in Excel as time zone independent it is being shifted to UTC and you are losing an hour. Date only fields are actual saved as the date at midnight. So if you are an hour behind UTC then midnight UTC is 23:00 your time. Or the other way around. But its probably some combination of how the date is input versus the way its being stored.

Was this reply helpful? Yes No
richmus 24 on at

Like
a
(0)

Report
Copy link

Link copied!

@Pstork1 Great spot, thanks!

When I've looked at the other date/time times, have noticed they're all an hour behind too (hadn't checked the times carefully before). We're in the UK and are currently an hour ahead of UTC with daylight savings....

I've checked the settings on the excel file, and they're set for English(UK) so should be using the same time zone?

Have changed the pickers to UTC and now the Today() function for the default date returns yesterday's date!

Any ideas how to fix? This hasn't happened on the other app I've been working on

Thanks,

Richard

Was this reply helpful? Yes No
Pstork1 69,599 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Excel doesn't use time zones, so the settings on the Excel file don't matter. The setting on the date and time picker in Power Apps does. So if you are an hour ahead of UTC and the date pickers are set to UTC then when selecting a date in the date picker it will return that date at midnight UTC which in your time would be yesterday at 23:00. You can either switch the date picker to local or use TimeZoneOffset to adjust the date time to allow for your time zone before saving in Excel. It can be tricky so you'll have to play around with it a bit. But your time zone is definitely where the 1 hour difference is coming from. That's also what is changing the actual date because midnight in one time zone is 11:00 PM yesterday in another.

Was this reply helpful? Yes No