Hi. I am trying to patch to a DateTime field in my source. I need to patch Now() with formatting, like this:
DateDeleted: Text(Now(),"[$-en-GB]yyyy/mm/dd hh:mm")
but I get the error "The type of this argument 'DateDeleted' does not match the expected type 'DateTime'. Found type 'Text'."
I understand the issue. I just need to patch Now() as a DateTime format but with "[$-en-GB]yyyy/mm/dd hh:mm".
My problem is that Now() gives me time 2 hours behind the current time. If I use the en-GB format, I get the correct time.
I know I can do this but hopefully there is a way without a workaround:
Solved! Go to Solution.
You cannot format a DataTime column. It is just a DateTime value. It is the responsibility of the interface to provide formatting. So, if your intention is to have date and time formatted different when viewed in your list, then you need to change the settings of your site to match what you want.
Dates are stored in their UTC value, so you need to look at the Regional Settings of your site in order to account for timezone offsets.
Your formula should just be DateDeleted: Now()
I hope this is helpful for you.
To adjust for the 2 hour timezone difference try adjusting the datetime using timezoneoffset.
DateAdd( Now(), TimeZoneOffset(Now()), Minutes )
That should automatically adjust the time to the local timezone.
If you use the DateAdd, you will have the wrong time in your data.
Either set your regional settings properly on the site or adjust based on local timezone when saving (which will still end up with the incorrect date/time in it)
Our timezone in regional settings is set to "UTC+2 Harare,Pretoria" which is correct for our region and we do not use Daylight Savings Time here. Our locale is set to English (United States) which is incorrect but I don't want to change that as it might affect other Sharepoint users. Am I correct that my timezone affects whether DST changes times?
Yes, that's why you should use timezoneoffset() to adjust the time.
For some reason, TimeZoneOffset() made it worse. I made a locale change on OneDrive, which is where my source is. I will give it a few hours to update and then try again. I did say SharePoint earlier which was a mistake.
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Check out our new Discover Your Career Path blog post series and get all the details.