Data Table shows wrong date

[BillZab]

Hi, I am having an issue with my Power app regarding saving the correct date. I use a datepicker on my input page of my app and the user selects the date. I have the hours and minutes set as not visible so there is no input from them. When I look at my Spreadsheet the date shows as the day before with the time set at 23:00. I have seen a solution that states the following:

Solution: Set the Default of the Date DATA CARD (not the Default Date on the Date picker) to

DateAdd(Thisitem.'StartDate', 5, Hours)

However, this does not work for me and gives a red cross saying there is an error in the formula. I have changed thisitem to thisitem.date which is what my datacard is set at, but it still does not accept it. I realise I would have to change the number from 5 to suit my United Kingdom timezone, but I can only do this if the formula was correct.

Any help appreciated. TIA.

[WarrenBelz]

Hi @BillZab ,

Firstly you need

DateAdd(
   ThisItem.'StartDate', 
   5, 
   TimeUnit.Hours
)

so see if that fixes the error

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[BillZab]

Hi, thanks for that. However, it does nothing to change the date entered in to the spreadsheet. If I select todays date (9th of April, 2024) from the date picker, the date & time entered in to the data table of the spreadsheet is '08/04/2024 23:00'. This is in UK format and is exactly one hour behind the date I entered. Incidentally, I am not interested in the time of day, but i can see no way of preventing the power app from adding the time.

I should also add, that I don't recall having this issue until daylight saving time kicked in 10 days ago.

[WarrenBelz]

@BillZab ,

Sounds like a time zone issue either on your PC or where the Excel sheet is stored,

[BillZab]

I agree. I have several users using the spps on their phones so the issue must be with the sharepoint file. I have no idea how to change the timezone of the sharepoint file storage. 

[WarrenBelz]

Hi @BillZab ,

Site Settings > Regional Settings > Time Zone

[BillZab]

Thanks Warren, I have changed this to the correct timezone, however it does not seem to change the time on the spreadsheet. Does it take a sometime to take effect do you know? It seems like my data entry send the correct date (today) with a time of 00:00 and the data is then saved to the timesheet data table as yesterday at 23:00. Is there any way I can change the time sent from the app to, say, today's date plus an hour. Your original answer of adding 5 hours did not add any time to the tha date and it still save as 23:00. Thanks.

[WarrenBelz]

@BillZab ,

This is more a maths issue and needs a bit of testing. Time zones and differences between the data source and the user are always challenging. Excel is also a terrible data source and does not handle (among other things) dates very well. I do not use it, so are not in a position to test anything.

[BillZab]

Thanks for your help. I have not found a solution but have created a workaround in a timesheet macro that adds an hour on to the date and time, thus changing the date to the correct date.