Excel 2010: Daylight Savings - calculate # of hours between timestamps for any given day

Question

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.

Answer 1

In absence of being offered a better method, I've now applied the following solution:

=Flowrate*(24+IF(AND(EOMONTH(Start_Date,0)-Start_Date<7,WEEKDAY(Start_Date,3)=6),CHOOSE(MONTH(Start_Date),0,0,-1,0,0,0,0,0,0,1,0,0),0))

---

How it works

In brief, working backwards:

CHOOSE(MONTH(Start_Date),0,0,-1,0,0,0,0,0,0,1,0,0)

For a given Start_Date, checks which month of the year it is and returns an offset of -1 hour if March and +1 hour if October.

WEEKDAY(Start_Date,3) = 6

For a given Start_Date, checks whether the current date is a Sunday, and returns TRUE if so.

EOMONTH(Start_Date,0)-Start_Date < 7

For a given Start_Date, checks whether it's less than seven days from the end of its month. Returns TRUE if so.

AND(EOMONTH(),WEEKDAY()=6)

Checks both of the above conditions. If it's a Sunday and within the last six days of the month (ie no more Sundays), return TRUE.

IF(AND(),CHOOSE(),0)

If above conditions are all TRUE, invoke CHOOSE to return the correct offset for the last Sunday of the given month. Otherwise, return zero. The returned offset is added to 24 to give the number of hours in the given day, which can then be multiplied against the hourly flowrate to give the day's total.