[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Convert date to number

Posted on 2011-02-10
Medium Priority
377 Views
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
Question by:ExpertHelp79
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 3
• +4

LVL 7

Expert Comment

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

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

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

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

ID: 34868551
test.xlsx
0

LVL 50

Expert Comment

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

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

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

Cheers

Dave
0

LVL 85

Expert Comment

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

LVL 85

Accepted Solution

Rory Archibald earned 2000 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

ID: 34868893
Your default date format is 'dd/mm/yyyy' and your source data format is 'mm/dd/yyyy'.

=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

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

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

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

Expert Comment

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

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

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

LVL 24

Expert Comment

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month14 days, 1 hour left to enroll