Solved

Excel 2010 date format issue

Posted on 2013-10-24
5
582 Views
Last Modified: 2013-10-24
Experts

Firstly I am in Australia so date format is dd/mm/yyyy

I have a spreadsheet which returns dates in the numeric code for the date - for example

if the user selects 2011 it returns 41285 for Jan 10, 41316 for Feb 10 and so on.
if the user selects 2012 it returns 41286 for Jan 11, 41317 for Feb 11 and so on.
if the user selects 2013 it returns 41287 for Jan 13, 41318 for Feb 13 and so on.

I think you get the drift of that bit.

When I try to use a custom format mmm-yy it returns Jan-13 for 41285, 41286, and 41287 and the same pattern for the other months.

So how do I get excel to show the correct date when using mmm-yy to convert the number code to the date?

It should show Jan-11 for 41285, Jan-12 for 41286 and Jan-13 for 41287 but it shows Jan-13 for all of these.
0
Comment
Question by:scs-paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 250 total points
ID: 39599279
mmm-dd
0
 

Expert Comment

by:Andray_1
ID: 39599320
To view the number as Date Format, select the cell and right click Cell on the Format menu. Click the Date option, and then click the format of the date you want!

Hope that help! having more trouble feel free to ask!
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39599351
Added Excel sheet with dates you used for easy reference...

Should work for 2010 also...
ExcelDate2007.xlsx
0
 
LVL 12

Accepted Solution

by:
Alan3285 earned 250 total points
ID: 39599352
Hi,

The date numbers you gave, correspond as follows:

41285      11 Jan 2013
41286      12 Jan 2013
41287      13 Jan 2013

You asked excel to give you Month-Year (MMM-YY) so it gives:

41285      Jan 2013
41286      Jan 2013
41287      Jan 2013

Based on what you wrote, it looks like you meant to ask it to give you Month-Day (MMM-D):

41285      Jan-11
41286      Jan-12
41287      Jan-13

For that, use the format:

MMM-D

Or, if you want to always have the day shown as two digits (Jan-07 rather then Jan-7) use:

MMM-DD



Personally, I tend to mostly use:

D MMM YYYY

since that is alway unambiguous, especially if you have to deal with a foreigner that uses some weird date format such as putting the least significant element in the middle or whatever.

Hope that helps,

Alan.
0
 

Author Closing Comment

by:scs-paul
ID: 39599386
Thanks all for the quick response.  The mmm-dd does the trick.

@byundt: thanks for the info I knew the codes were being output wrong but just not how to convert them to the format I needed.  The codes are being output by a BI frontend so I couldn't change the output to the correct code.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question