Convert date to number

Hello
I have a set of dates below i need to convert them to number. Also i am facing a problem
i am able to convert few to number by formating them to text, others dosent seem to react to the change . Please help.
Before
==========
02/01/2011
02/03/2011
01/31/2011
02/03/2011
10/01/2010
09/27/2010
09/28/2010

After
=======
40545
40604
01/31/2011
40604
40188
09/27/2010
09/28/2010






TIA
LVL 2
ExpertHelp79Asked:
Who is Participating?
 
Rory ArchibaldCommented:
Obviously, that was based on wchh's earlier formula!

You can remove the value function:

=IF(ISTEXT(A1), DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)),DATE(year(a1),day(a1),month(a1)))
0
 
SterlingMcClungCommented:
I think you system is set to use DD/MM/YYYY, therefore it can't conver 01/31/2011 as there is not 31 month.  First two dates should only be different by 2, but are different by about 60, which would be the difference between Jan 1 and March 2.
0
 
DaveCommented:
If the first string was in A1, ie say
09/27/2010

then this formula is B1
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

returns
27-Sep-2010

as a date

Cehers

Dave
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ExpertHelp79Author Commented:
I am getting this value
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
01/31/2011      15/05/2448
0
 
DaveCommented:
From your question it appeared that
01/31/2011
was a text value

Can you pls post a sample

Thanks

Dave
0
 
ExpertHelp79Author Commented:
Please check
test.xlsx
0
 
DaveCommented:
You have a mix of dates and text

My formula works fine on the text, not on the dates

is this the data in its original condition, or after you have adjusted it? Best we work with your data before you convert it

Cheers

Dave
0
 
wchhCommented:
Try formula below:
=IF(ISTEXT(A1), VALUE(DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))),VALUE(A1))
test-1-.xlsx
0
 
DaveCommented:
wchh

using my formula only on the text values leaves the "dates" as is

whereas
2/01/2011
should be 1-Feb-2011 based on the initial logic

thats why I asked to see the data before it had ben adjusted

Cheers

Dave
0
 
Rory ArchibaldCommented:
I suspect:
=IF(ISTEXT(A1), VALUE(DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))),DATE(year(a1),day(a1),month(a1)))
0
 
wchhCommented:
Your default date format is 'dd/mm/yyyy' and your source data format is 'mm/dd/yyyy'.

Try to the follow formula,
=IF(ISTEXT(A1), VALUE(DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))),VALUE(DATE(RIGHT(TEXT(A1,"mm/dd/yyyy"),4),LEFT(TEXT(A1,"mm/dd/yyyy"),2),MID(TEXT(A1,"mm/dd/yyyy"),4,2))))

or  change the default date format to 'mm/dd/yyyy' (Control panel->regional & language options)  and copy your source data again...
0
 
DaveCommented:
Rory,

Are you really on EE Mobile? :)

A little too late now - I am remembering why I loathe date function manipulations.

Cherers

Dave
0
 
wchhCommented:
If your first date is Feb 1, 2011, then use the follow below
=IF(ISTEXT(A2),VALUE(DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))),VALUE(DATE(RIGHT(TEXT(A2,"mm/dd/yyyy"),4),MID(TEXT(A2,"mm/dd/yyyy"),4,2),LEFT(TEXT(A2,"mm/dd/yyyy"),2))))
0
 
Rory ArchibaldCommented:
Dave,
Yes - on a train at the moment.
Rory
0
 
pradeepprahlladCommented:
One way out can be as below,

Select the column-->under data tab select text to columns-->select on "Delimited" click next--> untill you reach "convert text to columns wizard - Step 3 of 3" here select date and option MDY and hit the finish button.  This will reset all the dates in one single format.  Now convert this to number by going to format cells option and selecting numbers.

Hope this helps
0
 
SterlingMcClungCommented:
Your whole problem is rooted in the fact that the dates that you are working with are not in the same format as the computer's regional settings.  Because of this, when the dates were first put into the Excel file, some of the dates were recognized as dates(the ones that actually converted to an integer), and others were just entered as text into the cell(those that did not convert to an integer).  If you make sure that Excel recognizes your data properly, then manipulation of the data is a lot easier.  Because you have a mix of text and dates, you end up with very difficult to understand formulas, such as those that have been given.  If you don't have the option of recreating the Excel file to properly understand the data, then these types of formulas are required, but the best solution is to start with correct data in Excel.
0
 
Rory ArchibaldCommented:
Pending the Asker's response, I can say that my formula worked in his/her test file. :)
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.