Count days between text dates
 

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?

Count days between text dates
 

Try

=DATEVALUE(B1)-DATEVALUE(A1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Benny" wrote in message
...
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?

Count days between text dates
 

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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Benny" wrote:

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?

Count days between text dates
 

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

On Aug 11, 4:18*pm, Benny wrote:
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?

Count days between text dates
 

Shorter formula:

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

Luke M
*Remember to click "yes" if this post helped you!*


"Luke M" wrote:

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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Benny" wrote:

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?

Count days between text dates
 

On Tue, 11 Aug 2009 08:18:01 -0700, Benny
wrote:

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

Count days between text dates
 

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

"Pete_UK" wrote:

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

On Aug 11, 4:18 pm, Benny wrote:
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?

Count days between text dates
 

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

Pete

On Aug 11, 5:30*pm, Benny wrote:
It works thanks. *You just saved me about 30 minutes per day everyday.



"Pete_UK" wrote:
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

On Aug 11, 4:18 pm, Benny wrote:
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?- Hide quoted text -

- Show quoted text -

Count days between text dates
 

Why can't you change the dates to numbers?

One method for doing so.

Select A1 and DataText to ColumnsNextNextColumn Data FormatDateMDY and
Finish.

Same for B1


Gord Dibben MS Excel MVP

On Tue, 11 Aug 2009 08:18:01 -0700, Benny
wrote:

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?