Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2010 date format issue

Posted on 2013-10-24
5
Medium Priority
?
604 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 1000 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 20

Accepted Solution

by:
Alan earned 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

886 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