guys,
I have the col of data you see in image 1 below. the format is supposed to be dd/mm/yyyy. some of the data IS. however, in image 2 I am changing the bad cells via F1. and they do not change. see result in image 3. what could be causing this?
guys,
I have the col of data you see in image 1 below. the format is supposed to be dd/mm/yyyy. some of the data IS. however, in image 2 I am changing the bad cells via F1. and they do not change. see result in image 3. what could be causing this?
Given they're left aligned, I’m going to guess (which is all I can do with a picture) that those cells are text, not a numeric Date/Time.
Try using Text to Columns to convert them.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Well in all of those photos I was showing you guys that I literally tried to change the data type in the interface and that didn't work either. But I'll try text to columns and get back to you thanks
Please try at J1
=--(MID(TEXT(I1,"mm/dd/"),4,3)&REPLACE(TEXT(I1,"mm/dd/yyyy hh:mm:ss"),3,3,))
text to cols worked folks. thanks much. it was seriously messed up. the employer obviously has no clue what they're doing.
You're welcome.
If you've got text in a cell, it doesn't matter what it looks like, or what Custom format you use, it isn't going to change.
If you'd posted a file, rather than a picture, I could have confirmed the diagnosis and tested a solution, or two. And others could have tested their solutions. It just makes life simpler and a solution more quickly available (and tested).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Oh, and you're not changing the "data type", you're changing the way the underlying data is displayed. If it isn't numeric, you can't use a numeric format.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks