Count days between text dates

B

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?
 
L

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.
 
P

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
 
L

Luke M

Shorter formula:

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

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top