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

Day

Date

Time

Height 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

 

[Gerald Higgins]

Paul - so presumably your source data time is in UTC / GMT, yes ?

And the problem boils down to identifying whether the date / time for each entry is within BST, in which case add an hour.

Adding an hour is easy - it's just your original time plus 1/24 (assuming your data is stored as an Excel time value, not a text string).

 

[paulsolar]

Hi Gerald

You have hit the nail on the head. The dates and times are stored as excel (time is UTC as you suspected albeit it's only when we add the hour the UCT and GMT vary from each other for my purposes)

I'm struggling with the change over times when the hour is added at 1am and back to UTC at 2am on the last Sundays. It is important that I get this correct as it has a knock on effect to the lock closure times if they fall between these periods.

so for example, the lock is closed at 1am and would reopen say 3am but if the these times fall in this range on the first Sunday they would now be 1am and reopen at 4am. if this happens at the return to UTC it would be 1am and reopen 2am

hope that explains it a bit better

 

[paulsolar]

sorry Gerald, it can be done manually but i want to automate the whole process

 

[Fluff]

Whilst there's probably a simpler way, how about

=IF(AND(AND(B2>=EOMONTH(DATE(YEAR($B2),3,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),3,1),0),11),7),C2>(1/24)),B2<=EOMONTH(DATE(YEAR($B2),10,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),10,1),0),11),7),C2<(2/24)),C2+(1/24),C2)

 

[paulsolar]

Hi Fluff

Long time no bother. How are you keeping? wont be long till the 6 nations now

I think you are almost correct.

I've sorted the dates that have changed. Column D is tide height

I've had a little play with the formula, but not sure what I'm doing, as always you have me baffled using functions I've never seen

cheers

Paul

Friday	05/04/2019	01:12	1.19	LowWater	02:12:00	changed
Saturday	06/04/2019	01:44	1.09	LowWater	02:44:00	changed
Sunday	14/04/2019	01:44	7.34	High Water	02:44:00	changed
Saturday	20/04/2019	01:34	0.34	LowWater	02:34:00	changed
Sunday	28/04/2019	01:27	6.88	High Water	02:27:00	changed
Sunday	05/05/2019	01:12	1.2	LowWater	02:12:00	changed
Monday	06/05/2019	01:45	1.13	LowWater	02:45:00	changed
Monday	13/05/2019	01:33	7.68	High Water	02:33:00	changed
Sunday	19/05/2019	01:09	0.77	LowWater	02:09:00	changed
Monday	20/05/2019	01:49	0.8	LowWater	02:49:00	changed
Tuesday	28/05/2019	01:56	7.13	High Water	02:56:00	changed
Tuesday	04/06/2019	01:21	1.22	LowWater	02:21:00	changed
Tuesday	11/06/2019	01:14	8.12	High Water	02:14:00	changed
Tuesday	18/06/2019	01:27	1.25	LowWater	02:27:00	changed
Thursday	27/06/2019	01:56	7.35	High Water	02:56:00	changed
Wednesday	03/07/2019	01:02	1.24	LowWater	02:02:00	changed
Thursday	04/07/2019	01:49	1.05	LowWater	02:49:00	changed
Thursday	11/07/2019	01:50	8.13	High Water	02:50:00	changed
Wednesday	17/07/2019	01:10	1.52	LowWater	02:10:00	changed
Thursday	18/07/2019	01:47	1.44	LowWater	02:47:00	changed
Friday	02/08/2019	01:39	0.81	LowWater	02:39:00	changed
Friday	09/08/2019	01:14	7.94	High Water	02:14:00	changed
Friday	16/08/2019	01:30	1.4	LowWater	02:30:00	changed
Sunday	25/08/2019	01:15	7.27	High Water	02:15:00	changed
Saturday	31/08/2019	01:28	0.52	LowWater	02:28:00	changed
Sunday	08/09/2019	01:47	7.04	High Water	02:47:00	changed
Saturday	14/09/2019	01:08	1.31	LowWater	02:08:00	changed
Sunday	15/09/2019	01:39	1.19	LowWater	02:39:00	changed
Sunday	29/09/2019	01:12	0.37	LowWater	02:12:00	changed
Monday	30/09/2019	01:57	0.22	LowWater	02:57:00	changed
Monday	07/10/2019	01:09	6.81	High Water	02:09:00	changed
Monday	14/10/2019	01:12	1.19	LowWater	02:12:00	changed
Tuesday	15/10/2019	01:44	1.17	LowWater	02:44:00	changed

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[paulsolar]

sorry Fluff, i added a column to show which times have changed, none of the other times changed

 

[paulsolar]

Hi Gerald

your signature made me smile

 

[Fluff]

Hello Paul
Bet you enjoyed the Autumns

I would expect all those times to change as they are between 31st Mar & 27th Oct. Or have I missed something?

 

[Tetra201]

Here is my take on this:

=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")+2/24)/24