Adamant Excel Dates...

January 27, 2011 at 05:25:46
Specs: Windows XP
Hey there,

I would appreciate if i get help on Excel Date formatting.

Problem Statement: when I download the report from Business Intelligence, I get certain columns with Dates. The cell values are so rigid that, I can't format the dates which are in System driven format, and few are easy to convert to MDY, or DMY or whatever. But few, I cannot change.

Second Problem: When all the data is related to Quarter 1, when I try to format the date, I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011 when I pull it from reporting system.

The date should be 09-Feb-2011.

I have some 3000 lines of such adamant dates, help me to get out of this issue.

Positively seeking anybody's help.


See More: Adamant Excel Dates...

Report •

January 27, 2011 at 06:01:45
re: "I can't format the dates which are in System driven format,"

I don't know what you mean by "System driven format" but I can tell you that very often data that gets downloaded from websites and/or other applications ends up in Excel as Text instead of numbers or dates.

Since I can't see your spreadsheet from where I'm sitting, I'm going to take a guess and suggest that both of your problems are related to each other.

Let's start with Problem 2:

re: I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011

This statement does not really make sense. 02/09/2011 could actually be 02-Sep-2011 or it could actually be 09-Feb-2011, depending on how Excel (actually Windows) is set up. Let me explain.

The format in which Excel recognizes dates is based on the settings in the Regional and Language Control Panel, Customize button, Date tab. This is what determines whether the first 2 digits represent the day or the month.

My guess is that the values that you cannot format as dates are those where Excel can not recognize the month.

For example, since your system is recognizing 02/09/2011 as 02-Sep-2011 I'll assume that your Windows setting is something like dd-mmm-yyyy.

Excel has no problem changing 02/09/2011 to 02-Sep-2011 since 09 is a valid month, but it wouldn't know what to do with 02/15/2011 since there is no 15th month. My guess is that Excel decides that values like 02/15/2011 must be text and therefore cannot be formatted as a date.

Try changing your Control panel setting to something like mm-dd-yyyy and see if that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 27, 2011 at 06:12:05
Thanks bud, you have a valid point,

The region set is US, and the data comes from Europe Server.

The logic I don't understand is the date which says 31/01/2011 does not respond to the format change... I tried Custom Format to change the date, I used formula left and right and mid.

The solution I see is to get the data changed in Server itself... Let me try changing the parameters in Server...

Anyway, I will need more help on this. WIll update if I am not able to get the desired output... Thanks.

Report •

Related Solutions

Ask Question