Excel FORGOT the clocks went back — this has consequences
At the weekend, the clocks went back across Europe, allowing most of us to enjoy an extra hour in bed! In the UK, this occurred on 26 October at 02:00 BST, meaning the hour before was repeated.
The issue is Excel doesn’t know the difference between 01:00 BST (British Summer Time) and 01:00 GMT (Greenwich Mean Time). That creates a problem for cases where that hour is significant. For example, a hospital timesheet that records nightshifts.
Example
Natty the nurse was scheduled to work from 22:00 BST until 06:00 GMT across 25 and 26 October.
On the timesheet, this is listed as 8 hours — the same as the shift the day before and after. But it hasn’t taken into consideration the extra hour Natty worked, meaning she won’t be remunerated for it.
The solution is to add a dedicated column for adjusting the number of hours so her pay can be calculated correctly. Conditional formatting can also be used to highlight rows that have an adjusted value for easy readability.
