Count days between text dates

[Benny]

Hello, I need to count the number of days between dates also the months are
in spelled out. I can't change them to numbers. So, is there a way to count
days if cell A1 is Sep/5/2008 and cell B1 is Oct/25/2009?

 

[Niek Otten]

Try

=DATEVALUE(B1)-DATEVALUE(A1)

 

[Luke M]

Assuming you always have 4 digits for the year, how about this (all in one
line):

=DATEVALUE(MID(B1,FIND("/",B1)+1,FIND("/",B1,FIND("/",B1)+1)-FIND("/",B1)-1)&" "&LEFT(B1,FIND("/",B1)-1)&" "&RIGHT(B1,4))
-DATEVALUE(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&" "&LEFT(A1,FIND("/",A1)-1)&" "&RIGHT(A1,4))

There are numerous ways of manipulating date_text into the proper format
needed to numerically compare it.

 

[Pete_UK]

Assuming the earlier date is in A1 and the later date in B1, put this
in C1:

=DATEVALUE(MID(B1,5,LEN(B1)-9)&"-"&LEFT(B1,3)&"-"&RIGHT(B1,4))-
DATEVALUE(MID(A1,5,LEN(A1)-9)&"-"&LEFT(A1,3)&"-"&RIGHT(A1,4))

It returns 415 for your example data.

Hope this helps.

Pete

 

[Luke M]

Shorter formula:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(B1,"/"," ",1),"/",", "))
-DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1,"/"," ",1),"/",", "))

 

[Ron Rosenfeld]

Hello, I need to count the number of days between dates also the months are
in spelled out. I can't change them to numbers. So, is there a way to count
days if cell A1 is Sep/5/2008 and cell B1 is Oct/25/2009?

=SUBSTITUTE(SUBSTITUTE(B1,"/"," ",1),"/",", ")-
SUBSTITUTE(SUBSTITUTE(A1,"/"," ",1),"/",", ")

will work with most date formats in the Windows regional settings.

If it doesn't, the following, longer formula, should work with all:


=DATE(RIGHT(B1,4),MATCH(LEFT(B1,3),
{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
MID(B1,5,FIND("/",B1,5)-5))-DATE(RIGHT(A1,4),MATCH(LEFT(A1,3),
{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),
MID(A1,5,FIND("/",A1,5)-5))

--ron

 

[Benny]

It works thanks. You just saved me about 30 minutes per day everyday.

 

[Pete_UK]

Well, that's good to hear, Benny - thanks for feeding back.

Pete

 

[Gord Dibben]

Why can't you change the dates to numbers?

One method for doing so.

Select A1 and Data>Text to Columns>Next>Next>Column Data Format>Date>MDY and
Finish.

Same for B1


Gord Dibben MS Excel MVP