Converting BST DateTime to UTC Date from Dataverse data import

[MattTrollope]

Hi

I have a DateTime field that during BST is imported and stored as 23:00 on the previous day. When I remove the time from it (to enable searching specific days), it is then stored as one day earlier than it should be. Is there a straightforward solution to solve this issue?

Thank you for any assistance you can offer.

[mark_endicott · Accepted solution]

@MattTrollope - I assume you're doing this in Power Query, in which case the logic below creates a TRUE/FALSE column in a table to define BST dates. Hopefully you can use this to implement appropriate logic to subtract an hour off the time where necessary:

isBST = Table.AddColumn(#"Changed Type", "isBST", each 
    ([date_value] >= Date.StartOfWeek(#date(Date.Year([date_value]),3,28), Day.Sunday))
    and 
    ([date_value] < Date.StartOfWeek(#date(Date.Year([date_value]),10,28), Day.Sunday))),

[date_value] is the name of the date column in your table. 

Starting point would be to wrap this entire statement (after each) in an IF and then implement the necessary logic to minus one hour.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[MattTrollope]

Thank you for your response.

I was doing something very similar (although your formula for determining BST/UTC change dates is more elegant than mine, where I had hard-coded the March/September dates). I was hoping there might be a better way to approach the problem.

It works for many dates, but for some reason it returns nothing (no date) when I try to move the date from 30th June to 1st July (for all years). I'm aware this is a different issue, so this isn't the apprpriate place to take it further.