Adjusting Time for British Summer Time

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
670
Office Version
  1. 365
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


DayDateTimeHeight Mts Tide State

<tbody>
</tbody>

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
 
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
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Realised that my suggestion doesn't work properly, but Tetra201' far neater one does.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.:)
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top