CODEWITHSUNDEEP
exceldatenumbersdifferencedays
chaosKnight
chaosKnight

Reputation: 31

How to calculate the difference in days between dates that are numeric?

The dates are in a number format, what can I do? I tried the subtraction in other cases for the same year, it seems to result, but when it has the case below, it delivers a result of 8874, which is not what I want (it would be 5 days what i wanted).

Example in excel:

A1 = 20180103
A2 = 20171229

Any ideas?

Upvotes: 2

Views: 87

Answers (2)

Joe
Joe

Reputation: 626

Try this:

=
DATEVALUE(RIGHT(A1,2) &  "/" & MID(A1,5,2) & "/" & LEFT(A1,4)) 
- 
DATEVALUE(RIGHT(A2,2) &  "/" & MID(A2,5,2) & "/" & LEFT(A2,4))

This first converts the number into a format that excel can recognise as a date, then convert it to the date, and finally deduct one from the other.

Upvotes: 2

assylias
assylias

Reputation: 328659

An alternative to using DATEVALUE is to use the DATE function, which avoids an unnecessary string concatenation then date parsing:

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

Upvotes: 2

Related Questions