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

(0) Share

Report

Posted on by rwittels

510

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)

Categories:

Building Power Apps

All responses (11)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!

@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.
Verified answer

rwittels 510 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks. I am going to use
DateDeleted: DateAdd(Now(),2,Hours)
It works, but you gave me the information I needed.
Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@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)
StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!

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 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!

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 510 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!
Here is an interesting observation. I cannot patch to a DateTime field in my source with
Text(Now(),"[$-en-GB]yyyy/mm/dd hh:mm")
but I have a form on another screen which writes the exact same function, in Text format, to another DateTime field in my source without error.