CODEWITHSUNDEEP
excelworksheet-function
morganpdx
morganpdx

Reputation: 1876

Excel 2005: Difference between two days, counting hour difference

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:

Upvotes: 0

Views: 814

Answers (1)

arx
arx

Reputation: 16904

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

Upvotes: 0

Related Questions