Solved

Excel 2010 date format issue

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup". After a while, you have entered a loop for Auto repair which does not fix anything and you will be in a  panic as all your work w…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

20 Experts available now in Live!

Get 1:1 Help Now