Excel 2005: Difference between two days, counting hour difference

Question

Hi all - I've been trying to get this Excel function working correctly, and I've hit a wall. I'm trying to calculate the exact difference in days between two dates, taking the start time into account as well as the start day. Seems like this should be a common need?

Example:

   Start Date            End Date                 Expected Result
9/20/2010 8:00am      9/22/2010 3:00pm              2.3 days

I'm guessing at the .3, but you get the idea :) My current formula looks like this:

=IF(End < NOW(), 0, IF(Start >= NOW(), End-Start+1, End-NOW()))

It works almost perfectly...the only hiccup is if today is between the start/end dates, it only calculates full days, and does not include partials.

Here are the parameters for this function:

• Today is before start date: Calculate full number of days
• Today is between start and end date: Calculate exact days left, taking start/end hours into account
• Today is start date: Calculate exact days left, taking start/end hours into account
• Today is start date, hours left: Calculate exact days left, taking start/end hours into account
• Today is end date, after end time: zero
• Today is end day, hours left: Calculate exact days left, taking start/end hours into account
• Today is after end date: zero

Answer 1

I've just tried this in Excel 2000 and the clause with the problem [End - Now())] does include decimal places in the answer. Have you set the column format to include decimal places in the display?

However, I would change [End-Start+1] to [CEILING(End-Start,1)]. This rounds up the value to the nearest whole number of days. This gives:

=IF(End < NOW(), 0, IF(Start >= NOW(), CEILING(End-Start,1), End-NOW()))