How to convert UTC to UK Time
Enjoyed your extra hour in bed today? 🛌 😴
That’s right. Across Europe, clocks went back 60 minutes at 01:00 UTC (Coordinated Universal Time). In the UK, we moved into Greenwich Mean Time (GMT) a second after 01:59:59 BST (British Summer Time). Another five months will see us doing the reverse.
Nevertheless, dealing with dates and times is confusing enough without throwing in time zones, formats, conventions, leap years, and so forth. But it’s even worse when it’s not communicated clearly.
Take the LinkedIn archive, for example, which you can download as a CSV file to see all your posts and stats in tabular format.
Each post has a timestamp, but the column’s header merely says ‘Date’, so it’s open to interpretation.
It would be easy to think it pertains to your local time zone — I certainly did at one point. However, after closer inspection, I realised it does indeed use the UTC format.
Example
A range of UTC dates are contained in A2:A7. These have been converted to UK Time in B2:B7.
Formula in B2:
=LET(
date,A2:A7,
year,YEAR(date),
month,{4,11},
date2,DATE(year,month,1-WEEKDAY(DATE(year,month,0)))+(1/24),
date
+
(
(
(date>=TAKE(date2,,1))
*
(date<DROP(date2,,1))
)
/24
)
)The formula works by checking to see if the UTC value is in the BST period.
Inside LET, named values are defined for:
date— the UTC dates in A2:A7.year— the year of each UTC date.month— an array of 4 and 11, which represent April and November, respectively.date2— an array containing the serial date numbers for the last Sunday in March and October.
In the DATE’s day argument, WEEKDAY(DATE(year,month,0)) returns the last day of March (31) and October (31) and counts backwards until the final Sunday is landed on. As there are six days between (and including) 31 to 26 March and three from 31 to 29 October, this returns {6,3}. Subtracting each from 1 gives {-5,-2}.
Piecing it all together, the DATE statement returns {45011,45228}, which are the serial numbers for 26/03/2023 00:00 and 29/10/2023 00:00. Adding (1/24) returns 0.041666667, which is the equivalent of one hour in time format (01:00). This ensures the dates start at 01:00, making the final values {45011.0416666667,45228.041666667}.
Serial numbers use reader-friendly dates below.
In the calculation, (date>=TAKE(date2,,1)) extracts only the first value from the array (26/03/2023 01:00), whereas (date<DROP(date2,,1)) does the same for the second (29/10/2023 01:00).
Conditional tests check if the UTC date (26/03/2023 01:00) is greater than or equal to 26/03/2023 01:00 and whether it’s less than 29/10/2023 01:00.
Both parts evaluate to TRUE, meaning 1 is returned when they are multiplied. This is then divided by 24 to give 0.041666667 (00/01/1900 01:00), which is added to the original date.
