Solved

Convert date to number

Posted on 2011-02-10
20
369 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:ExpertHelp79
  • 5
  • 4
  • 3
  • +4
20 Comments
 
LVL 7

Expert Comment

by:SterlingMcClung
ID: 34868399
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34868438
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
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34868472
I am getting this value
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))
01/31/2011      15/05/2448
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34868519
From your question it appeared that
01/31/2011
was a text value

Can you pls post a sample

Thanks

Dave
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 34868551
Please check
test.xlsx
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34868783
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
 
LVL 8

Expert Comment

by:wchh
ID: 34868802
Try formula below:
=IF(ISTEXT(A1), VALUE(DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))),VALUE(A1))
test-1-.xlsx
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34868817
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34868882
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34868888
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
 
LVL 8

Expert Comment

by:wchh
ID: 34868893
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34868914
Rory,

Are you really on EE Mobile? :)

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

Cherers

Dave
0
 
LVL 8

Expert Comment

by:wchh
ID: 34868924
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34868939
Dave,
Yes - on a train at the moment.
Rory
0
 

Expert Comment

by:pradeepprahllad
ID: 34869062
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
 
LVL 7

Expert Comment

by:SterlingMcClung
ID: 34936022
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35179212
Pending the Asker's response, I can say that my formula worked in his/her test file. :)
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35356924
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now