Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count number of dates falling in the next seven days | Excel Worksheet Functions | |||
Count of days between dates | Excel Discussion (Misc queries) | |||
Count days inside 2 dates | New Users to Excel | |||
how to count number of days between 2 dates? | Excel Discussion (Misc queries) | |||
I want to count days between two dates including start date | Excel Worksheet Functions |