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
Super User

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,040)