I have some dates in text format and I want to change them into date format(number format). I am using DATEVALUE function but it is showing #VALUE error.
I have also tried changing my system's date format but nothing seems working. I have attached an example workbook.
@Rchamoli.... Your data are not "dates in text format". Instead, they are numeric Excel dates that are displayed with a Custom format.
For example, A3 is the numeric value 28101. It appears to be 07-12-1976 because of the Custom format dd-mm-yyyy .
The DATEVALUE function wants to see the text "07-12-1976" (if your system date form is DMY), for example, which can be entered in many ways.
But why are you using DATEVALUE, in the first place, if your data are already numerice Excel dates?
Note: Crossposted to https://answers.microsoft.com/en-us/...0-1792d5bffaed .
Try this:
=A3+0
Set column B formatting to SHORT DATE.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
The dates are coming in left which means they are in text format.... they must come in right side of the cell to be in number format... that is what i am unable to do...
Thanks @AliGW it worked in my example workbook but the problem remains same in my main workbook. What could be the reason??
Let's see a workbook that shows the problem, please.
Try:
=TRIM(A3)+0
I have attached a reference workbook
You have applied a left alignment in column B... so the values align to the left.
Remove it.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Both my suggestions work. Change the column B formatting to GENERAL and you will see a 5-digit serial number- this PROVES that the text has been converted to a date.
You need to better understand how dates are handled by Excel and how to determine what is and what is not a date.
We now know you have also cross-posted here:
https://chandoo.org/forum/threads/va...-format.48898/
Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.
Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:
1) if you have cross-posted ANYWHERE ELSE, and
2) the URL of the cross posts.
That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Looks can be deceiving, and the format of the cell does not matter.Originally Posted by Rchamoli
Use formulas like =ISTEXT(A3) and =ISNUMBER(A3) to determine the type of the cell value.
-----
Sorry, I did not see that until after I submitted. But I think it is only fair that I be able to "defend" comments that I had posted before your restriction.
Last edited by curiouscat408; 09-10-2022 at 04:23 AM.
I am so Sorry for that @glenn kennedy ... I will keep that in mind from next time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks