How to make dates round correctly. RoundDown() for dates?

[martinav]

It appears if you use UTC() in a flow, into a date only (not time) sharepoint field, this causes issues in powerapps.  I need dates to round properly when using them in my apps.  Currently, when I use a date field item, it will round up to the next day if the time is after Noon.  It should not do that.  The day sould always stay the same until Midnight is reached.  Thus, RoundDown() would be the proper logic to apply...  However, can this function be used with dates?  If so, I need some syntax help.

Thank you.

[v-xida-msft]

Hi @martinav,

I don't think the RoundDown() function could achieve your needs.

The RoundDown() function is used to round a number rather than a Date value. More details about RoundDown function, please check the following article:

RoundDown function

I think this issue is relaetd to Time Zone. Please make sure the Time Zone of your PowerApps is the same as that with your local Time Zone.

In addition, I think the DateAdd function and TimeZoneOffset function could also help in your scenario. More details about the DateAdd function and TimeZoneOffset function, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-dateadd-datediff

Best regards,

Kris

[martinav]

@v-xida-msft,

Yes, I have verified my timezone settings long ago.  I am uncomfortable using offset functions, because I feel like this is some issue with either a bug, or some other more obscure setting that needs resolved.  If I use an offset, then I'm just masking the problem, and there is no telling when it may start working again, then all of the dates are wrong in the other direction.  I must keep all dates in my list as UTC, in its "pure" form.  

The strange issue is, the dates viewed in the sharepoint default view is fine.  When I use thisitem.date in a label, etc, that is where the date shows wrong.  Is there something else to look at??

This is correct in Sharepoint Site Regional Settings

This is correct  in Windows 10:

 Actual example made today:

My powerapps view shows (using Thisitem.<DateName>) in Labels (Engineering completion shows a day late!):

Flow action were Request Approval was given (good UTC time/date):

Flow action where Completion date is recorded (good UTC time/date):

Flow action where 

f

Fulll time/date stamp recorded to sharepoint in a multi line text column (data is perfect):

[martinav]

@v-xida-msft,

I have changed the columns to Date & Time.  THat makes it more clear that it looks like the powerapps use of date only, rounds the date up.  How else can you explain this?

Here is a side by side for one record.  Left is column set to Date, the right to Date & Time.

See the "Final Approval Date:" above.  Left is the wrong date, right is the correct date.  Still look slike rounding.  If the hour is above Noon, it rounds the date up.  Perhaps I'll try turning on time, and then using formatting.

[martinav · Accepted solution]

Ok, well... it looks like I fixed my own problem again.  I still wish someone who really knows what they are talking about could explain to me why this is happening.  So, this is what I did:

• Changed the date columns to "Date and Time" format, as opposed to "Date Only"
• Use Text(<Source>,"mm/dd/yyyy") to format to strip the time off.  The resulting is the date that will roll over at mighnight, as opposed to Noon.  

I feel that this is an acceptable solution because it will not affect changes in DST.

[rc_rc_rc]

Say your sharepoint date value is called SPDate , try this in the app
 
Date(Year(SPdate),Month(SPdate), Day(SPDate))    - should reconstruct the date with time set to 00:00:00 

(Also: 
  Date(Year(Now()),Month(Now()), Day(Now()))) 
gets todays date with time 00:00:00  - I needed that to check if datepicker value equals "today" without the time stamp part of now() messing it up)