Changing type to date is causing date to go to next day

[EaglesTony]

Hi,

  I have this data coming in:

  When I change the type to "Date", it shows me 11/18/2024 instead of 11/19/2024 ???

[Omid_Motamedise]

it is because of time zoon. your value is in time zoon + 00:00 and when you convert it into date your time zoon will be added into it.

[EaglesTony]

Keep it UTC how ?...This data is coming from another system...is there a way I could adjust it back 5 hours ?

[lbendlin]

Yes, Power Query offers functions to add, remove, or change timezones. 

Converting a datetimezone to date will recalculate the date.  Removing the timezone first will not recalculate the date.

You can choose which way to do this.

[EaglesTony]

I am in the Eastern Time Zone.

[lbendlin]

That would explain it.

What is your expected result? And why?

[lbendlin]

You seem to live in a timezone west of Greenwich? (But not that much, less than 20 hrs)

[EaglesTony]

So your saying, since I am 5 hours behind, PowerBI doesn't take into account my region settings(EST) and changes it back 5 hours (in this case would be 11/18/2024 11pm, I assume) ?

[ronrsnfld · Accepted solution]

Looking at it another way, it DOES take into account your region settings. The original is of type datetimezone and the +00:00 indicates it is UTC. When you convert it to type date, it changes it to the date in your local timezone. For END_DATE that happens to be the previous day. 

How to handle this depends on what you are trying to accomplish. If you want to keep the Date part the same as if you were using UTC, then you can remove the timezone information.

[EaglesTony]

Yes, I just want to keep the date part.

This is the solution:

1) First remove the timezone using DateTimeZone.RemoveZone 

2) Convert it to a Date 

[lbendlin]

correct.  Stuff like this is the main reason why everything should be kept in UTC.