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). Theres 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
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). Theres 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
