LinkBack URL
About LinkBacks
Hi
Can anyone help me to make a formula that convert the text 05/30/2019 into 30.05.19?
I have tried with formating the cell. Using datevalue, and =date(right,left,mid). None of them seems to do the trick.
Hi
Can anyone help me to make a formula that convert the text 05/30/2019 into 30.05.19?
I have tried with formating the cell. Using datevalue, and =date(right,left,mid). None of them seems to do the trick.
What was the DATE formula you tried, and what happened? The basic syntax you posted is correct.
Rory
Try this
in B1
Formula:
Please Login or Register to view this content.
v A B 1 5/30/2019 30.05.19
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Real date or text looking like date? A sample sheet would help
I just get a #value error, or the exact same date as in a1. See sample sheet
I recommend converting all of the dates at once.
This can be done by highlighting the column > Data > Text to Columns > Next > Next > Date: MDY > Finish
cell A1 will have new date, together with a lot of other cells, many times over again. that's why I want a formula that converts it, so I don't need to change the value manually
Agree with 63falcondude; by far the most practical option.
A couple of formula alternatives, assuming the original entry is always of the form mm/dd/yyyy:
1) Office 365: =0+CONCAT(MID(A1,{4,1,7},{3,3,4}))
2) All versions: =0+(MID(A1,4,3)&MID(A1,1,3)&RIGHT(A1,4))
In both cases format the cell as Custom/dd.mm.yyyy
Regards
This was the solution that worked!Originally Posted by XOR LX
(the one with converting all the cells at once doesn't work when I pass a new date in with 05/30/2019, as it doesn't convert it to 30.05.2019 without me manually going through the converting process again)
Ok, glad to help!
Cheers
Hi
Suddenly I can get the formula to work anymore. See attached spreadsheet
A1 is a date, not text.
Aaa, thank you that made it correct again. I have a problem though, every time I paste the values into the spreadsheet, the cell that I chose as text now automatically changes to date, is there any way to make the cell stay as a text cell even though something is paste into it?
When you paste, use paste value. That way the formatting of the cell will remain unaltered.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Why? Why not just test if it's a number (dates are stored as numbers) and if it is, use it directly, otherwise convert it using the formula you had originally.
If I paste 06/11/2019 as values I get 43775 in the text box, which gives me 15/07/2106 in the cell where the formula is
Ignore me - I misunderstood what you are copying. Sorry!
I found the problem, Calculation options seems to go back to Manual when I past something into the cells. It works when I past the values and the option is set to manually. It's when I change it to automatic and then past the values (no matter how), that they seems to change to date automatically (as it probably recognize how the value is written, or something like that).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks