How to convert a set of UTC dates/times into GMT/BST

I'm trying to convert a set of dates/times that are stored in Universal Coordinated Time (UCT) format into London time, which switches between British Summer Time (BST) and Greenwich Mean Time (GMT) each year.

Let's be crystal clear about how the UK time system works, using 2023 as an example.

On 26 March 2023 (the last Sunday) at 01:00:00 UTC (01:00:00 GMT), the clocks go forward one hour, meaning the time becomes 02:00:00 BST.

On 29 October 2023 (the last Sunday) at 01:00:00 UTC (02:00:00 BST), the clocks go back one hour, meaning the time becomes 01:00:00 GMT.

2023-10-27_11-13-42 2.png

The formula I've written in B2 almost achieves this, except it doesn't jump forward or back at the aforementioned times stated because it's a second out.

Formula:

Please Login or Register  to view this content.

Currently, when it's 26 March 2023, the time has to be 01:00:01 UTC (01:00:01 GMT) for the jump forward to trigger, thus becoming 02:00:01 BST.

And when it's 29 October 2023, the time has to be 01:00:01 UTC (02:00:01 BST) for the jump backwards to trigger, thus becoming 01:00:01 GMT.

How can I adjust the formula so it works perfectly?

Or try this formula:

Please Login or Register  to view this content.