how to change date format in excel sheet

Microsoft Office 2007 professional (aca...
June 23, 2011 at 03:42:42
Specs: Windows XP
how to change the date format form dd/mm/yyyy to mm/dd/yyyy.

I already tried by using >format cell > number tab > date , but it is not working. Solution for this will be highly appreciated.

Thanks in Advance.


See More: how to change date format in excel sheet

Report •


#1
June 23, 2011 at 04:01:21
What do you mean "it's not working"?

Are you trying to change the default for your system or just for some cells in Excel?

To change the default for your system you would change it in the Control Panel under Regional And Language Settings.

Perhaps we need some more details.

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


Report •

#2
June 23, 2011 at 04:30:07
Hi There,

Thanks for your response.

I mean i have one excel sheet, where in it has 7 days data. Data is something like this, for the first 10lines of first day (02/05/2010), for the second 18 lines of second day (03/05/2010) and so on. I want to change dd/mm/yyyy to mm/dd/yyyy respectively. Please Advice.


Report •

#3
June 23, 2011 at 04:40:52
It's possible that Excel is not recognizes the data as dates.

Trying this:

Pick a cell that has a date in it, let's says it's A1. In another cell enter this:

=MONTH(A1)

Does it return the correct month of the date you are testing?

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


Report •

Related Solutions

#4
June 23, 2011 at 04:46:18
Indeed this works. But i dont wan to deal wid that. I just wan to change d format as i already said dd/mm/yyyy to mm/dd/yyyy. I dont wan to go to each n every cell to change dis format. it has around 2000+ lines.

Thanx


Report •

#5
June 23, 2011 at 06:06:58
re: "But i dont wan to deal wid that"

Let's make sure we are on the same page. I am trying to help you without being able to see your spreadhseet or see what it happening when you try to change the format. By asking you to try the MONTH function, I was simply trying to determine if Excel was recognizing the contents of the cells as dates.

In your OP your said: "I already tried by using >format cell > number tab > date , but it is not working."

I then asked What do you mean "it's not working"?

Again, since I can't see your spreadsheet from where I am sitting, I don't know what not working means. So I'll ask again: What does "not working" mean?

What happens when you try to change the date format?

Have you tried entering a date such as December 12, 2011 (something with a 2 digit month) and setting the format to mm/dd/yyyy?

Date formats in Excel can be finicky especially with dates like 02/05/2011 where both 05 and 02 could mean either the Month or the Day. Since I can't duplicate your "problem" all that I can do is ask questions and suggest things to try as a means to narrow down the possible cause.

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


Report •

#6
August 23, 2011 at 19:27:49
Please let me add into the question Sricharan was asking.

I have a data file received as a .csv file - in it the date format shows as MM/DD/YYYY.

I want to change the date to show as DD/MM/YYYY which is the way we display dates in New Zealand. There are 2500 records and two columns presently have these false dates - how do I easily (quickly) change them without having to open each cell and move the month and day to the correct place?


Report •


Ask Question