Sharepoint adding 2 hours to time given

[RachelUCN]

I have a flow that gets rows from an excel spreadsheet and adds them to a sharepoint list - this includes a couple of columns than include dates and times (note - I store the date/times as strings in excel because I was having issues getting power automate to read them)

The flow adds them to sharepoint

it looks fine in the flow checker outputs

But on sharepoint it adds 2 hours

I'm assuming it's something to do with time zones as the times in the spreadsheet are in British Summer Time (GMT + 1) but the sharepoint site it set to UTC + 1 and I can't figure out why that would ever add 2 hours?

As a side note - I'm not really sure how UTC interacts with BST 

Thanks for any help

[abm]

Hi @RachelUCN 

Please check your SharePoint DateTime settings?

Thanks

[RachelUCN]

Hi

Do you mean this?

Thanks

[takolota]

@RachelUCN 

SharePoint takes any date value you give it for a date column & assumes it is in UTC time. Then on the front-end it adjusts to the time zone you set the site for.

Try converting the time zone to UTC time where it is entered into SharePoint from the flow.

This was the bane of my existence while building SharePoint & Outlook synced calendars, adjusting for time zone & daylights savings time.

[okeks]

The Z at the end of the times means it is Zulu time, so atually UTC +0.

This is always UTC +0.

Excel itself doesn't store the timezone next to the datetime so the flow will alsways treat it as UTC+0.

Here is where you get the first hour.

I'm not an Sharepoint expert but my guess would be that Sharepoint converts it for you from UTC+0 to UTC+1 (BST) and so you gain another hour here.

[abm]

Hi @RachelUCN 

Thanks for your reply. Is that time zone settings right?

[RachelUCN]

I converted to UTC and that worked after I realised that I need to set everything to London (UTC + 0) not somewhere else (UTC + 1) because even though it doesn't say so it does know that London is in BST (UCT + 1) right now. 

Very confusing but it's (hopefully) sorted now!

[RachelUCN]

It wasn't! I didn't realise the time zones given account for daylight savings so thought I had to manually select a timezone an hour ahead (UTC + 1) rather than trusting that it would use that even though it said London was UTC + 0.

Very confusing!

[abm]

Hi @RachelUCN 

So your SharePoint setting is different than your local time zone. Change the time zone to your local time and see if the dates are showing correctly or not?

Thanks