Solved: Date and Time errors

JFras · ‎04-26-2023

Maybe someone can help me to understand what is happening here.

I have a date & time column called [DueDate]. I have created the below flow to compare the output of my [DueDate] column in Get Item on the left with the output of convertTimeZone function shown on the right.

The DueDate column has the date April 29th, 2023 @ 5pm, displayed as 04/29/2023 5:00pm

The Get Item returns the date as 2023-04-30T00:00:00Z. This makes sense, I am in Mountain Standard Time (UTC-7), so converting 5pm MST to UTC should be midnight the next day (ie 7 hrs later).

Now, when I try to convert the Get Item date output from UTC to Mountain Standard Time it is returning 18:00. That is only a -6hr difference. Why is it converting differently one direction from the other?

JFras · ‎04-26-2023

Solution:

The site was still set to the default time zone setting of Pacific time. Once I changed it to MST everything worked fine, had nothing to do with daylight savings.

To note however, when you change the default date and time setting for the site, all information in your date columns change. This is a very new list so it was only 5 items but could be an issue for longer lists. I feel that the date that was entered by the end user should be retained when the default date changes as end users are generally not thinking about what time zone their sharepoint tenant is set to when they enter data.

View solution in original post

Nived_Nambiar · ‎04-26-2023

Hi @JFras

Have you tried this expression for converting utc to specific timezone ?

convertFromUtc(utcNow(),'Mountain Standard Time')

This shows the timezone difference b/w utc and MST time by 6 hours when using current date

Hope this helps

JFras · ‎04-26-2023

Doesn't seem to make a difference.

I entered the expression as

convertFromUTC([DueDate], 'mountain standard time')

JFras · ‎04-26-2023

I see what you were asking now, @Nived_Nambiar .

The expression you suggested does return the correct time.

convertFromUTC(utcnow(), 'mountain standard time')

So maybe the problem is with my date column? We are currently on Mountain Daylights Savings Time, so this might be why it is off by an hour. How can I see what time zone my column is in?

JFras · ‎04-26-2023

Solution:

The site was still set to the default time zone setting of Pacific time. Once I changed it to MST everything worked fine, had nothing to do with daylight savings.

To note however, when you change the default date and time setting for the site, all information in your date columns change. This is a very new list so it was only 5 items but could be an issue for longer lists. I feel that the date that was entered by the end user should be retained when the default date changes as end users are generally not thinking about what time zone their sharepoint tenant is set to when they enter data.

Nived_Nambiar · ‎04-26-2023

Hi just checked online MST to UTC converter , it shows 6 hours difference, would it be due daylight saving time ?

as convertfromutc/ timezone will considers the daylights savings hours as well ?

JFras · ‎04-26-2023

Apparently the convert date and time expressions handle daylight savings automatically.

The problem was with my site settings being on the default Pacific Standard Time still.

Thanks for the help! Your initial suggestion led me to the conclusion that the problem was with the list data itself and not the functions converting to and from UTC.

Regards,

Jacob