cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rwittels
Resolver I
Resolver I

How to patch Now() to a DateTime field with formatting

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:

 

DateDeleted: DateAdd(Now(),2,Hours)

 

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks. I am going to use

DateDeleted: DateAdd(Now(),2,Hours)

It works, but you gave me the information I needed.

View solution in original post

10 REPLIES 10
RandyHayes
Super User III
Super User III

@rwittels 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks. I am going to use

DateDeleted: DateAdd(Now(),2,Hours)

It works, but you gave me the information I needed.

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

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 I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

@rwittels 

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)

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
StalinPonnusamy
Community Champion
Community Champion

Hard corded values will end up issue with daylight savings. Use regional settings to use correct timezones.

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?

Pstork1
Dual Super User III
Dual Super User III

Yes, that's why you should use timezoneoffset() to adjust the time.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
rwittels
Resolver I
Resolver I

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.

rwittels
Resolver I
Resolver I

Even with the correct timezone and locale in Regional settings, using DateAdd still gives the correct time and using TimeZoneOffset() breaks it.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,792)