Excel 2005: Difference between two days, counting hour difference

0
votes

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
1
excelworksheet-function
I am only going to mention that you should take care to consider daylight savings time. – Mike Axiak

1 Answers

0
votes

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()))