Compare dates (one cell not in date format)

[craigcsb]

Hi all,

I have one column of cells in date format (6/14/2005) and anothe
column of cells which also contains a date pulled from a database bu
this second column is not in date format, it comes from the databas
like this 2005Jan20. I need to compare the two dates to work out th
number of days between the dates but I can't convert the second date t
date format.

Is there any way I can work out the number of days between the tw
dates?

Thanks in advance
Crai

 

[Guest]

If the month is always three letters and the day is always 2 digits, uses a
helper column and enter
=MID(A1,5,3)&" "&RIGHT(A1,2)&" "&LEFT(A1,4)
Copy down to the end of the data
copy this column and paste special values on top of itself
Select this column
<Data><Text to Column> next-next Select date
The input data is now in date format.

 

[Domenic]

Try...

=A1-DATEVALUE(MID(B1,5,3)&" "&LEFT(B1,2)&", "&LEFT(B1,4))

...where A1 contains your first date, such as 6/14/2005, and B1
contains your second date, such as 2005Jan20.

Hope this helps!

 

[Dave Peterson]

I don't even think you need the formula.

Just do Data|Text to columns. Choose fixed width (don't have any separator
lines).

Choose ymd as the format and finish up.

Format the column the way you like.

 

[Ron Rosenfeld]

Hi all,

I have one column of cells in date format (6/14/2005) and another
column of cells which also contains a date pulled from a database but
this second column is not in date format, it comes from the database
like this 2005Jan20. I need to compare the two dates to work out the
number of days between the dates but I can't convert the second date to
date format.

Is there any way I can work out the number of days between the two
dates?

Thanks in advance
Craig

To convert your database derived dates to Excel date format:
1. Select the range of dates to be converted.
2. Data/Text To Columns
Next
Next
Column Data Format
Date YMD
Finish

3. Then merely subtract one from the other. In other words, if one set of
dates is in column A, and the other set in column B, then =B2-A2 will give the
difference in days. Be sure to format this as General or as Number.


--ron

 

[Guest]

When I tried the ext to columns this format did not convert. I may have done
something wrong, but went ahead and did the convert before.