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
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
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
so you can see that you formula is not correct.
If I want to do mm/d/yyyy with your formula I would need
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 firstname.lastname@example.org, 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.