Compare dates (one cell not in date format)
 

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


--
craigcsb
------------------------------------------------------------------------
craigcsb's Profile: http://www.excelforum.com/member.php...o&userid=24710
View this thread: http://www.excelforum.com/showthread...hreadid=382752

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.

"craigcsb" wrote:


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


--
craigcsb
------------------------------------------------------------------------
craigcsb's Profile: http://www.excelforum.com/member.php...o&userid=24710
View this thread: http://www.excelforum.com/showthread...hreadid=382752

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!

craigcsb Wrote:
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


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=382752

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.


bj wrote:

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.

"craigcsb" wrote:


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


--
craigcsb
------------------------------------------------------------------------
craigcsb's Profile: http://www.excelforum.com/member.php...o&userid=24710
View this thread: http://www.excelforum.com/showthread...hreadid=382752



--

Dave Peterson

On Tue, 28 Jun 2005 05:42:41 -0500, craigcsb
wrote:


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

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.

"Dave Peterson" wrote:

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.


bj wrote:

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.

"craigcsb" wrote:


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


--
craigcsb
------------------------------------------------------------------------
craigcsb's Profile: http://www.excelforum.com/member.php...o&userid=24710
View this thread: http://www.excelforum.com/showthread...hreadid=382752



--

Dave Peterson