Solved

Excel 2010 date format issue

Posted on 2013-10-24
5
552 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now