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

11 REPLIES 11
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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.

Pstork1
Dual Super User
Dual Super User

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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
Super User
Super User

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?

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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,080)