scs-paul
asked on
Excel 2010 date format issue
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Added Excel sheet with dates you used for easy reference...
Should work for 2010 also...
ExcelDate2007.xlsx
Should work for 2010 also...
ExcelDate2007.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
@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.
Hope that help! having more trouble feel free to ask!