Solved

Excel 2010 date format issue

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Outlook Free & Paid Tools
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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