I have two columns of dates in excel 2007 - a Date Created and a Date Resolved - and I need to compare the two to produce a column containing the Days Lapsed. (This could also be presented in hours, if that's easier.) Does anyone have any suggestions, I'm at a loss.

A simple subtraction of the 2 cells, formatted as a number, should give you the results you're looking for. 06/21/2010 - 4/5/2010 = 77

Well, now I feel dumb. Thanks! (Both the self-deprecation and the gratitude are genuine :))

Don't be embarrassed. Dates and Times in Excel can be tricky. The background is this:

Excel stores Dates and Times as numbers, with the integer portion of the number being the day (using 1/1/1900 as Day 1) and the decimal portion being a fraction of a day.

For example, I just entered =NOW() in a cell and it displayed:

10/25/2010 15:19

I formatted the cell as a number and it displayed:

40476.638603125

because 10/25/2010 is the 40,476th day since the beginning of Excel time, and

.638603125 * 24 = 15.326475 or about 15 1/3 hours into the day.

That's why you can do "simple" math with Excel Dates and Times.

The point where it gets very tricky is when you are adding or subtracting times that cross the pesky Midnight threshold.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History