Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-18-2014, 09:30 AM
Ulodesk Ulodesk is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2013
Word 2013 Expert Cert
Date-formatting problem
 
Join Date: Sep 2009
Location: Virginia
Posts: 781
Ulodesk is on a distinguished road
Post Date-formatting problem

I noticed that in a worksheet that I have been keeping for years, many of the dates in one column, entered as, e.g., 4/15/01, now show as a five-digit number, such as 40987. Interestingly, this has not happened on another, similar sheet in the same workbook.



Checking the cell formatting, the problem cells appear to have been formatted as a non-asterisked date format of the type indicated above, so, as I understand it, they shouldn't be influenced by other changes. I found one that had an asterisked date format and changed it to non-asterisked, but nothing happened.

I was guessing that perhaps daylight savings time on the computer might have caused a problem, but I don't recall this happening in the past, and anyway the date format has no time in it.

Can I restore these? They are purchase dates for various items and while it's possible I have them in a saved backup, I don't know how long the problem has existed.

Last edited by BobBridges; 06-08-2014 at 06:16 AM.
Reply With Quote
  #2  
Old 05-18-2014, 09:48 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

What is an "asterisked date format"?

EDIT on't bother I've got it
Reply With Quote
  #3  
Old 05-21-2014, 09:54 AM
BobBridges's Avatar
BobBridges BobBridges is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

I don't have it; what is an "asterisked date"?

Also, Ulodesk, did you get your problem solved? If not, can you post the workbook so we can take a look at it?
Reply With Quote
  #4  
Old 06-04-2014, 04:43 PM
Ulodesk Ulodesk is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2013
Word 2013 Expert Cert
Date-formatting problem
 
Join Date: Sep 2009
Location: Virginia
Posts: 781
Ulodesk is on a distinguished road
Default Dates problem

Sorry for delay; I was not available for a while.
I am attaching an example of rows from the spreadsheet I mentioned. All I did was delete sensitive info from other columns (not the date one) and also two other worksheets, which don't involve dates.

Thanks for checking back; I hope you figure it out.
Attached Files
File Type: xlsx Example.xlsx (16.4 KB, 1 views)
Reply With Quote
  #5  
Old 06-08-2014, 05:56 AM
BobBridges's Avatar
BobBridges BobBridges is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Question We need some help, here.

Ok, this is very odd. I need some input from others here. The OP has some date fields that Excel knows are dates, but that it insists on displaying in General format even after I tell it to use some other format. Observe the attached screen shot:

1) In the formula bar at the top is "2006-5-31". This is how I know that Excel knows it's a date.

2) The selected cell, C5, says "38868"; Excel is displaying the date in General Format.

3) In the Format-Cells dialogue at the right is the format that I set for that cell. I saved that setting and the date's format did not change from General. Then I entered the dialogue again, and it's still set to dd-mmmm-yy; Excel didn't change the format to anything else, it's simply ignoring it.
Attached Images
File Type: png x.png (79.5 KB, 7 views)
Reply With Quote
  #6  
Old 06-08-2014, 06:15 AM
BobBridges's Avatar
BobBridges BobBridges is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 674
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

Got it! Ulodesk, I stumbled across this purely by accident, looking around for some setting that might be doing this to you, and at first when I found the right one I didn't even notice it had fixed the problem. Do this:

1) On the ribbon, go to Formulas.

2) In the fourth ribbon section, entitled "Formula Auditing", notice that option H, "Show Formulas", is highlighted.

3) Turn it back off.

When I mouseover that option, it says it's to "Display the formula in each cell instead of the resulting value". It also says the keyboard shortcut for this option is <Ctrl-`>; I expect you hit that keystroke accidentally without knowing what you did. At least, I know that several times I've accidentally hit keystroke sequences that did something unexpected, unwanted and ununderstood.

I've never noticed that Formula-Auditing section before; now that I'm looking, it seems to have some useful features. In fact, the only thing I've ever used in the whole Formulas bar is the Name Manager. Thanks for your question, Ulodesk; I foresee a fruitful exploration coming up.
Reply With Quote
  #7  
Old 06-08-2014, 02:27 PM
Ulodesk Ulodesk is offline Date-formatting problem Windows 7 64bit Date-formatting problem Office 2013
Word 2013 Expert Cert
Date-formatting problem
 
Join Date: Sep 2009
Location: Virginia
Posts: 781
Ulodesk is on a distinguished road
Default

BobBridges, bravo! I use Excel in such a limited way, I don't imagine I ever would have found it. You're undoubted right about the keystroke; I use as many keyboard shortcuts as I can remember, and I probably was trying to put an acute accent over an e somewhere.

Many thanks.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date-formatting problem Changing Actual Finish dates changes original Finish dates SomewhereinTX Project 3 12-20-2013 10:25 AM
8pt fonts not showing up correctly in PowerPoint tech PowerPoint 1 02-08-2013 06:59 AM
Date-formatting problem currency not showing correctly on mail merge tonywatsonmail Mail Merge 1 12-07-2011 04:29 PM
Date-formatting problem margins don't set correctly dawit Word 1 05-10-2011 09:47 PM
Date-formatting problem TOC Not Updating Correctly Nigel1985 Word 1 05-27-2010 07:19 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:11 PM.


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