Report

Changing format of date in Excel [Solved/Closed]

Ask a question drizn - Last answered on Sep 10, 2017 at 10:57 PM by cheesecake
Hello,

I have an excel sheet which has lots of cells in a column with the date format as - day month date time year & I wish to convert that to dd/mm/yy. How can I do that, pls help.

eg - I need to convert "sat Jun 29 00:00:00 PST 1996" to "29/06/96". How can I. Any help is appreciated.
Helpful
+62
plus moins
go to Data, text to columns, make sure Delimited is highlighted and press next. check the space box then next. highlight the fields you do not want an select, do not import the finish. this will remove the time stamp. you can them format your column by right clicking, format etc.
Was this answer helpful?  
EEKAY- Apr 18, 2009 at 02:53 AM
Thanks a lot ... it was very very useful. I was fighting with this for the past 2 hrs. Within few seconds it is resolved
michele- May 16, 2009 at 03:29 PM
i need to remove the time from colume a it has both date and time and the time is unecessary??
rej- Aug 26, 2013 at 12:13 AM
thank you...
Shiba- Jul 8, 2017 at 02:59 PM
Thanks so much!! Worked perfectly!!
Reply
cheesecake- Sep 10, 2017 at 10:57 PM
Thanks for this.
Reply
Helpful
+22
plus moins
It took me a while to find a solution to an existing list of dates, (which is what I am assuming you are working with). In my case I had international date (dd/mm/yyyy) and wanted a U.S. view (mm/dd/yyyy).
Select the Column range you want to convert (must be continuous), then click on 'Data', click 'Next' twice (ignoring the Delimted vs Fixed Width Choice & the Delimiter character, then select DMY or MDY from the drop down list. In my case, since my OP system is U.S. but the spreadsheet came from overseas, I had to go with a DMY to 'clean up"

Credit to http://www.ozgrid.com/Excel/convert-excel-date-formats.htm.
Nick- Apr 21, 2010 at 11:15 PM
This works ok but for any date that is numbered 20 or higher it does not convert. For example 20/11/11 does not convert but 03/07/11 converts correctly to 07/03/11. Any ideas why or how to resolve?
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Apr 22, 2010 at 04:36 AM
When you choose date format while using column to text, make sure that you have the right date format (MDY or DMY in the drop down
Nick- Apr 22, 2010 at 08:54 AM
Yes I did that. It still doesn't work when the day of the month is 20 or greater.
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Apr 22, 2010 at 09:01 AM
I just had 20/11/11 in a cell and used text-to-column functionality and choose DMY format. For me it did convert to proper date. Are you sure you had the right DMY format in the drop down (as the date 20/11/11 is in Day /Month/year format
Jayachandran.K- Aug 16, 2013 at 01:35 AM
Dear Rizvisa1 ,

Thanks .
Helpful
+11
plus moins
The Best Solution (May be temprary ) is

Go and change your system date format to whatever format you want in
and it will change

go to

Change date and time settings
than go to change callender date/time setting

thanks
Helpful
+10
plus moins
go to Format Cells, click on Number Tab and then select Custom and type dd/mm/yy under Type:
drizn- Oct 8, 2008 at 05:50 PM
Tried that but it isnt working.
chomps- Nov 19, 2008 at 04:19 AM
I have exactly the same problem I cant change the format of the date in a cell. I have 34000+ cells and I am not going to change each one manually by retyping in the date

Please tell me if you got this right....
joey- Mar 29, 2010 at 02:39 PM
this is IT. I scoured the web for this. thanks.
Person- Jun 10, 2010 at 11:58 AM
Yours is the only one that helped! Thanks!
Helpful
+8
plus moins
Thanks a lot, this one is very helpful.
Helpful
+6
plus moins
Hi everyone, my problem is sort of the same as above, but slightly different.

I have data that says 08/01/09 21:43:40 (as in mm/dd/yy hh:mm:ss) which is exactly what I want. Problem is, is that in the display bar, it converts the time to AM/PM so in the display bar it says 08/01/09 9:43:40 PM.

the display bar seems to be the 'computer' version, as in when I look on text to columns, delimited, it displays the data is AM/PM time with an AM PM column.

i thought if I copied and pasted the data it might retain the original displayed value (without the AM PM as I want it) but it always shows the AM PM.

HELP, I need to format it so I definately is in 24hr time.

thanks
Ben- Dec 7, 2009 at 08:24 AM
Is there any way which I can chnage the format of the date as given below,

13-JAN-2009 to 13-01-2009

I have a whole batch to convert like dat...
Helpful
+6
plus moins
Look at the first answer from Karina.
Helpful
+2
plus moins
first u go in format which u see in top menu. than go to cells or click calls button, than go to number n than u see the category details. than go to date n change your format. okok
SID- Jan 30, 2009 at 07:50 AM
Gentlemen,

I need to enter the date and time in the format 02/02/2009 23:59:59 in the excel sheet.But I am unable to do that.
Under the option Format cells -->Number Tab ----> I donot have the option to enter in this format.Please help me out here.
confy- Feb 18, 2009 at 10:23 AM
Check the regional settings of your computer
masih- Jan 23, 2010 at 03:30 AM
hey I just need the great excel formats for school like if you have please senc it to my email address,


thanks
Helpful
+2
plus moins
Hello All.. if you have a value on Excel column with for example 10/21/09 12:00:36am and you want to get only the Date. You can use the Text to Column functionality and fixed with. The value then will be separated into to columns thus gaving you A column to have 10/21/09 and B column 12:00:36am

Hope this helps
Helpful
+2
plus moins
Go to Format Cells, click on Number Tab and then select Custom and type.
Helpful
+2
plus moins
My problem was with data entry, some dates apparently being accepted, some not.

I was trying to type in the date of a month in excel as mm/dd/yy some it would accept and put on the right side of the cell and others it would reject and put on the left side of the cell. Turns out that it was autoformatting the cells as dd/mm/yy and if the second value was greater than 12 it would automatically reject it as a date. Turns out this is because excel 2007 uses the information from the region settings part of control panel in Windows. Mine were set up as English (Canada). If you change the short date format here it will automatically be reflected in Excel, you may need to select another english option in my case English (United States) and select the appropriate short day format. Excel will now recognize the entry as mm/dd/yy. Once you are done your data entry and you can be sure your data is entered corretly (months are months and days are days) you can then choose format cells to make the data look the way you want independent of what the region settings are.

If you know you will be pasting data from another source that will be formatted different than your default region settings it will likely be worth changing region settings before pasting to ensure data integrity.
Anthem- May 12, 2010 at 09:00 AM
This fixed my annoying problem of entering 27/5 for May 27 and it not formatting the text at all or it automatically formatting it as mm/dd/yyyy. Now it puts this in 27/05/2010.

thanks!
Riys- Jul 15, 2010 at 03:19 AM
thank you so much for your valuable comments to all
imneng- May 19, 2011 at 10:51 PM
Great Advice!! Finally I can fix its. Thank a lots
Helpful
+1
plus moins
Tried all of the above (to create YYYY/MM/DD), and although the output on the screen changes, as soon as the file is saved the format reverts to DD/MM/YY.

Grrrrrr.
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - Apr 7, 2010 at 11:39 PM
Upload a sample work book with this problem at some share site like http://www.speedyshare.com/ and post link
Helpful
+1
plus moins
u need to change language in the regional lang settings if you need the format to appear as dd/mm/yyyy then select lang as english uk
Helpful
+1
plus moins
It took me a while to find a solution to an existing list of dates, (which is what I am assuming you are working with). In my case I had international date (dd/mm/yyyy) and wanted a U.S. view (mm/dd/yyyy).
Select the Column range you want to convert (must be continuous), then click on 'Data', click 'Next' twice (ignoring the Delimted vs Fixed Width Choice & the Delimiter character, then select DMY or MDY from the drop down list. In my case, since my OP system is U.S. but the spreadsheet came from overseas, I had to go with a DMY to 'clean up"

Credit to http://www.ozgrid.com/Excel/convert-excel-date-formats.htm.

_________________
[url=http://moviesonlinefree.biz]Watch A Nightmare On Elm Street Online Free[/url]
Helpful
+0
plus moins
thank you issue solved! :)
Helpful
+0
plus moins
Dear

for this just right click on the cell and select format cell option, in number tab go to custom, and at the place of type, write dd/mm/yy and press ok.

This might solve ur problem.

Urs frnd Vivek
Helpful
+0
plus moins
I just want to get the value in HH:MM format (Hours, not the time) to converted in DD:HH:MM format. Such as- 11:30hrs. as 00:11:30 (DD:HH:MM)...how can I do in in excell?? Pls. mail me at - tirthapratim.dutta@gmail.com
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - May 25, 2010 at 05:24 PM
why not format the cell to that ?
Helpful
+0
plus moins
rizvisa1 4482Posts Thursday January 28, 2010Registration date ContributorStatus January 6, 2016 Last seen - May 26, 2010 at 06:11 AM
what do you mean by 18043 Fred ?
Helpful
+0
plus moins
TRY GOING TO EACH WORKSHEET
select OPTIONS / TRANSITION AND UNTICK BOTH
TRANSITION FORMULA EVALUATION AND TRANSITION FORMULA ENTRY

NO IDEA WHY BUT NOW ALL MY SPREDSHEETS IN THE WORKBOOK ENABLE ME TO FORMAT 13-Mar-10 without telling me I have formula error

ps dont ask me why

Add comment - Edit - Permalink (#2)


Add comment

Ask your question » I have an answer
Helpful
-2
plus moins
how to show 000 in one excel sheet cell
1 2 Next

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!