Adjusting Time for British Summer Time

[paulsolar]

Hi All

I think this is a question to test the chaps who specialise in difficult formula

I am working on something that requires me to adjust tide times from UTC to a format that accommodates the seasonal changes in the UK for British Summer Time. Below is an explanation from the UK Government of this.
"In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.
The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).
When the clocks go back, the UK is on Greenwich Mean Time (GMT)."

I have a spreadsheet with the information which has at the moment 5 columns


[TABLE="width: 393"]
<tbody>[TR]
[TD="class: xl64, width: 80"]Day[/TD]
[TD="class: xl63, width: 99"]Date[/TD]
[TD="class: xl63, width: 64"]Time[/TD]
[TD="class: xl63, width: 74"]Height Mts[/TD]
[TD="class: xl63, width: 76"] Tide State[/TD]
[/TR]
</tbody>[/TABLE]

where Day is say Wednesday
where Date is say 16/01/2019 (UK format, dd/mm/yyyy)
where Time is say 14:51:00
where Height Mts is the height of Low or High Water
where Tide State is either Low Water or High Water

What I want to do is use a formula in Column F that will add the one hour where required year on year to save me having to do it manually as I have to calculate lock closure based tide heights and times.

I'm convinced that it is possible but try as I might I cannot get it to work.

Any help would be greatly appreciated. I dont mind either a vba or formulaic solution.

Thanks in advance

cheers

Paul

 

[paulsolar]

Hi Fluff, the autumn was magnificent

i think i have explained it wrongly

all the times between those dates should have the hour added, not just the ones that fall withing the time slot in between the two dates, it is just the ones at the start and end of the change over dates that have to be done at exactly the correct time otherwise people will expect the locks to be closed or open when they aren't if the time isn't changed properly

 

[Fluff]

Realised that my suggestion doesn't work properly, but Tetra201' far neater one does.

 

[paulsolar]

Hi Guys

Tetra, i have tested yours and amended times to check and it works perfectly.

I understand the functions except the INTL and what the "1111110" is doing, so I've no idea how it works, but it works brilliantly.

Fluff, i can see what yours is doing and i think i didn't explain it correctly, when i read my explanation again it could be read two ways and depending how you read it yours worked as well.

The rest of the job now for me is quite straight forward as it's nothing more than nested if statement.

thanks again for your help

cheers

Paul

 

[Tetra201]

Glad to have helped.

Here is a brief explanation for the WORKDAY.INTL function:

"1111110" means that Sunday is the only workday in a week;
WORKDAY.INTL(DATE(YEAR(B2),4,1),-1,"1111110") returns the workday (Sunday) immediately before the 1st of April, that is, the last Sunday in March;
WORKDAY.INTL(DATE(YEAR(B2),4,1),-1,"1111110")+1/24 returns 1 AM on the last Sunday in March, and so on...

 

[paulsolar]

Hi Tetra, many thanks for the explanation, I've come to a conclusion. I thought I knew quite a bit about excel, but I only know a bit more than the average user. I need to get the books back out and learn some more.

 

[gmcgough600]

Isn't it supposed to be:

=C2+AND(B2+C2>=WORKDAY.INTL(DATE(YEAR(B2),4,1),-1,"1111110")+1/24,B2+C2 < WORKDAY.INTL(DATE(YEAR(B2),11,1),-1,"1111110")+1/24)/24

Tetra201's code works for March clock change but changes it back an hour late in October.

 

[MARK858]

but changes it back an hour late in October.

What time do you expect it to go back in October?

 

[gmcgough600]

What time do you expect it to go back in October?

At 2am (BST) they should go back 1 hour, Tetra201's code puts them back at 2am GMT/UTC (which is 3am BST).