SOLVED
Home

Date Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-212295%22%20slang%3D%22en-US%22%3EDate%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212295%22%20slang%3D%22en-US%22%3E%3CP%3EI%20wonder%20if%20anyone%20could%20help%20me%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20probably%20really%20simple%20but%20I%20can't%20for%20the%20life%20of%20me%20get%20Excel%20to%20do%20as%20I%20would%20like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20all%20our%20company%20graphs%2Fpivots%20etc%2C%20date%20is%20displayed%20as%2018%2F06%20(yy%2Fmm)%20I%20have%20tried%20using%20a%20custom%20format%20but%20it%20always%20defaults%20to%20a%20full%20date%2001%2F06%2F2018%20in%20the%20display%20bar%20even%20though%20displaying%20correctly%20in%20the%20data%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20541px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F37106i4C30569D8B6FFAA5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2205-07-2018%2008-32-35.jpg%22%20title%3D%2205-07-2018%2008-32-35.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIt%20causes%20havoc%20for%20my%20pivots.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20have%20the%20column%20format%20my%20dates%20as%20YY%2FMM%20to%20display%20in%20the%20data%20and%20in%20the%20display%20bar%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EClair%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-212295%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDate%20format%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%202016%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-236788%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-236788%22%20slang%3D%22en-US%22%3ENo%20this%20problem%20has%20been%20around%20for%20years%20and%20is%20due%20to%20how%20data%20is%20entered%20or%20imported%20into%20Excel.%20If%20the%20source%20of%20the%20data%20is%20text%2C%20Excel%20will%20mark%20the%20values%20as%20text.%20So%20you%20need%20a%20way%20to%20convert%20the%20values%20to%20dates%20before%20creating%20pivottables.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-222530%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-222530%22%20slang%3D%22en-US%22%3EI%20had%20the%20same%20issue%20and%20this%20worked.%20Is%20it%20a%20glitch%20in%20the%20new%20excel%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212389%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212389%22%20slang%3D%22en-US%22%3EI%20don't%20think%20you%20can%2C%20at%20least%20I%20could%20not%20find%20a%20way%20to%20do%20so.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212340%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212340%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20do%20I%20flag%20my%20thread%20as%20resolved%20please%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EClair%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212339%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212339%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20x%20a%20million.%3A-)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212328%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212328%22%20slang%3D%22en-US%22%3EIn%20your%20file%2C%20the%20value%20in%20A2%20ia%20not%20a%20true%20date%2C%20but%20in%20fact%20text.%20But%20as%20soon%20as%20you%20edit%20that%20cell%2C%20Excel%20will%20try%20to%20assess%20what%20you%20entered%2C%20recognize%20the%20entry%20as%20a%20date%20and%20enter%20a%20true%20date%20into%20the%20cell.%20To%20avoid%20all%20of%20this%2C%20before%20making%20ANY%20edits%20to%20your%20data%2C%20Format%20the%20column%20in%20question%20as%20Text.%20Then%20do%20your%20edits.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212319%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212319%22%20slang%3D%22en-US%22%3E%3CP%3ERather%20than%20having%20one%20entry%20per%20month%2C%20it%20creates%20one%20column%20per%20date%2C%20I%20inherited%20the%20spreadsheet%2C%20so%20older%20months%20are%20actually%20in%20YY%2FMM%20when%20I%20copy%20that%20down%20and%20change%20the%20month%20in%20the%20formatting%20bar%2C%20it%20goes%20straight%20to%20full%20date.%26nbsp%3B%20Even%20though%20that%20column%20is%20custom%20formatted%20YY%2FMM%20it%20still%20puts%20new%20entries%20to%20full%20date%20(in%20the%20ribbon%20even%20though%20displaying%20in%20the%20actual%20data%20as%20YY%2FMM)%20so%20the%20custom%20formatting%20changes%20the%20display%20but%20not%20wherever%20it%20pulls%20from%20and%20display%20in%20the%20ribbon.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20(I%20hope)%20an%20example%20of%20my%20spreadsheet%20which%20I%20hope%20will%20help%20explain%20what%20I%20mean%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EClair%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212310%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212310%22%20slang%3D%22en-US%22%3EWhat%20does%20it%20do%20for%20your%20pivots%20that%20you%20do%20not%20like%20precisely%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502121%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20a%20similar%20problem%20however%20converting%20my%20column%20to%20text%20didn't%20help.%20In%20the%20cell%20the%20date%20is%20displayed%20thus%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E05-Oct-1980%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20ribbon%20the%20date%20is%20displayed%20thus%3A%3C%2FP%3E%3CP%3E10%2F5%2F1980%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20strip%20out%20the%20year%20from%20the%20dob%20so%20I%20can%20calculate%20the%20age.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%2C%3C%2FP%3E%3CP%3ECatherine%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502517%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502517%22%20slang%3D%22en-US%22%3EA%20simple%20way%20is%20to%20use%20the%20YEAR%20function%20in%20another%20cell.%20To%20calculate%20the%20age%20you%20can%20also%20use%20the%20DATEDIF%20function%20as%20shown%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FDATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FDATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c%3C%2FA%3E%3C%2FLINGO-BODY%3E
Clair Huntley
New Contributor

I wonder if anyone could help me please?

 

Its probably really simple but I can't for the life of me get Excel to do as I would like.

 

On all our company graphs/pivots etc, date is displayed as 18/06 (yy/mm) I have tried using a custom format but it always defaults to a full date 01/06/2018 in the display bar even though displaying correctly in the data

05-07-2018 08-32-35.jpg

It causes havoc for my pivots.

 

How can I have the column format my dates as YY/MM to display in the data and in the display bar?

 

Please?

 

Thank you

Clair

10 Replies
What does it do for your pivots that you do not like precisely?

Rather than having one entry per month, it creates one column per date, I inherited the spreadsheet, so older months are actually in YY/MM when I copy that down and change the month in the formatting bar, it goes straight to full date.  Even though that column is custom formatted YY/MM it still puts new entries to full date (in the ribbon even though displaying in the actual data as YY/MM) so the custom formatting changes the display but not wherever it pulls from and display in the ribbon.

 

I have attached (I hope) an example of my spreadsheet which I hope will help explain what I mean

 

Thank you

Clair

 

 

Solution
In your file, the value in A2 ia not a true date, but in fact text. But as soon as you edit that cell, Excel will try to assess what you entered, recognize the entry as a date and enter a true date into the cell. To avoid all of this, before making ANY edits to your data, Format the column in question as Text. Then do your edits.

Thank you x a million.:-)

how do I flag my thread as resolved please?

Thank you

Clair

I don't think you can, at least I could not find a way to do so.
I had the same issue and this worked. Is it a glitch in the new excel?
No this problem has been around for years and is due to how data is entered or imported into Excel. If the source of the data is text, Excel will mark the values as text. So you need a way to convert the values to dates before creating pivottables.

@Jan Karel Pieterse 

Hi

 

i have a similar problem however converting my column to text didn't help. In the cell the date is displayed thus:

05-Oct-1980

 

On the ribbon the date is displayed thus:

10/5/1980

 

I am trying to strip out the year from the dob so I can calculate the age.

 

Please help,

Catherine

A simple way is to use the YEAR function in another cell. To calculate the age you can also use the DATEDIF function as shown here: https://support.office.com/en-us/article/DATEDIF-function-25dba1a4-2812-480b-84dd-8b32a451b35c
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies