Excel/datevalue

Advertisement


Question
I have a date of diagnosis for specific disease, number of cases are 246. The form of the date is "18/3/2014" and I want to convert it to txt or a value to be able to calculate and a survival curve need to be done as well
Data start from row 3
Column "O" has date of diagnosis
Column "P" has date of outcome
I tried this formula
=DATEVALUE(RIGHT(O3,2)&"/"&MID(O3,5,2)&"/"&LEFT(O3,4))
But every-time the result is Zero "0"
Can you please help me to overcome this problem
Looking forward to hear from you
Thank you very much and regards

Answer
Nahid,

when I put 18/3/2014 in a O3 as a string, then I put your formula in and check how the argument is being constructed,  I get

"14//2/18/3"

so you can see that you formula is not correct.

If I want to do   mm/d/yyyy with your formula I would need

=DATEVALUE(MID(O3,4,1)&"/"&LEFT(O3,2)&"/"&Right(O3,4))

that produces   "3/18/2014"  as the argument to DateValue and DateValue converts that to the dateserial number 41716  which if I format the cell, I can format it with any valid date format and it will display the data of month:  March, Day:  18, year:  2014

Now all the complexities that need to be considered.  

You question shows you as being in Saudi Arabia.  I have no knowledge of Arabic, but I seem to remember that Arabic is constructed right to left rather than left to right as is done where I am (the United States).   So I don't know how your data is written or how Left and Right and Mid work if you have a regional version of Excel that is in Arabic.   My answer must necessarily be in the US English version of Excel.

Another issue is that you say you want to convert you date to text.  I entered your date as text because if it is not already text, then using text functions like Left, Right and Mid will not work.  A true date is stored as an elapsed number of days from a base date.  In Windows, this base date is midnight as 1 Jan 1900 begins.  so that means it would be stored as the number 41716 as I stated above.  But doing left, right and mid against a date serial will be working on 41716 - not 18/3/2014  as you show.  If it is stored as a date, then you can make it a string with the text function   

=Text(O3,"mm/dd/yyyy")  or however you want it formatted.

So there are more possibilities - but I can't guess at all the things that could be true.  So I have provided some general advice.  Hopefully you can use some of that information to solve your problem.

If you want to send a workbook to twogilvy@msn.com, and tell me exactly what you want, I can try to solve it with US English and hopefully if you open it in another regional version of Excel, it will automatically convert it to work with you settings - I can't say whether it will or it won't.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.