I have a checksheet which cross-checks various water measurement points, reading the average rates from around thirty different meters out of another system. I've automated this checksheet so a VBA script can run the sheet over and over for a given date range (usually one month at a time) and extract the results to another page.
The UK had its Daylight Savings yesterday (Sunday 25th October 2015) and this is throwing up issues for me. All the metered readings are giving me an 'hourly average flowrate' and I've been multiplying up by 24 to give a daily rate. But I'm now conscious that two days per year, 24 is the wrong multiplier to use.
So, given a Start_Date of 25/10/2015 00:00 and End_Date of 26/10/2015 00:00, how can I get Excel to calculate the correct number of hours between them, that will also work for all other days each year? I want to be able to reliably run this checksheet across any given period and for each day to calculate the daily total properly dependent on whether it has 23, 24 or 25 hours. Every conversion I've tried so far returns the difference between the two timestamps as being '1' since Excel counts the 25h as one day.
Edit: For the record, the UK changes on the last Sundays of March and October.