Search code examples
stringexceldatetimeexcel-formulaworksheet-function

Number of days in date range

I need to evaluate two cells ([start_date] and [end date]) and get the difference in days. [end date] - [start date] works great if the dates are the same month, the problem occurs if the [start date] is from the previous month.

For example if the [start date] is 20140527 and the [end date] is 20140603 Excel returns 76. I also tried using the DATEDIF formula but Excel throws a #NUM! error.

Please watch this simple screencast I created.

Solution

  • Judging from the results you mention, Excel has no awareness that the numbers represent dates. Your approach works within a month because, say, the 15th is 5 days ahead of the 10th whereas the 15th of next month is more than 5 days ahead of the 10th of this month.

    So to be able to subtract one from the other Excel needs to be made aware that these numbers represent dates, which can be achieved in various ways, one of with is with a formula of the kind: 

    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

    having made such conversions simply taking the difference should provide the results you would like, for example 7 rather than 76 (which comes from 603 - 527).