Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-11-2022, 01:54 PM
trevorc trevorc is offline Displayed date not showing correct format Windows 7 32bit Displayed date not showing correct format Office 2013
Competent Performer
Displayed date not showing correct format
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 01-12-2022, 10:50 AM
kilroyscarnival kilroyscarnival is offline Displayed date not showing correct format Windows 10 Displayed date not showing correct format Office 2019
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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
Reply With Quote
  #3  
Old 01-13-2022, 01:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Displayed date not showing correct format Windows 7 64bit Displayed date not showing correct format Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Displayed date not showing correct format 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
Displayed date not showing correct format Mail Merge with correct format amentinho Mail Merge 14 10-10-2015 09:23 PM
Displayed date not showing correct format Way to get date on each slide correct? AZG PowerPoint 1 03-18-2015 07:20 AM
Displayed date not showing correct format Correct Format needed TonyB Mail Merge 3 05-03-2011 01:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft