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
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!
Originally Posted by craigcsb
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
>
>
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
<craigcsb.1rc2ia_1119956706.6557@excelforum-nospam.com> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks