#1
|
|||
|
|||
Displayed date not showing correct format
Hi All,
this is a strange one, when i format a cell for date= dd/mm/yy the date entered as 12/1/22 is shown as 1/12/22 in the cell, but in the formula bar is is showing correctly as 12/1/22 We use international date format not US Any ideas as to why to is happening ??? regards Trevor |
#2
|
|||
|
|||
It would help to be able to see it.
But it's likely that your dates are being perceived as text, and therefore the formatting is not applying correctly. Perhaps you have some spaces or other invisible characters in your date cells, or maybe, especially if they've been pasted or extracted in from text, they just need refreshing. You can do this a couple of ways. You can highlight the column, and use the Text to Columns button on your Data ribbon. Use Delimited by Tab, as there won't be any tabs inside the cell, so you won't be parsing on text by accident. That should do it. You can also use a helper column next to it and use the formula "=VALUE(A2)" to convert the text date to a number, and reformat that. But the down-dirty way of doing it for me (I used to work with huge sheets of exported numbers stored as text) was to use Find/Replace. For me, I didn't have dates, so I had to replace every integer from 0 to 9 with itself, which I did with a simple macro. Try highlighting the column with the dates in it and hit Find/Replace. If they're dates from this year or last year, replacing every 2 with a 2 (in other words, making no material changes but causing the cell to be refreshed) should touch every cell. Best, Ann |
#3
|
||||
|
||||
Please post a sample sheet. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Date is displayed in the wrong format | shawn.low@cox.net | Mail Merge | 1 | 12-28-2019 02:17 AM |
Why is the date/time of comments (and other changes) not displayed in balloons or Reviewing Pane? | paulkaye | Word | 2 | 12-31-2015 12:38 AM |
Mail Merge with correct format | amentinho | Mail Merge | 14 | 10-10-2015 09:23 PM |
Way to get date on each slide correct? | AZG | PowerPoint | 1 | 03-18-2015 07:20 AM |
Correct Format needed | TonyB | Mail Merge | 3 | 05-03-2011 01:10 AM |