Search : in
By :

Changin format of date in excel

Last answer on Nov 17, 2009 9:02:45 am GMT drizn, on Oct 8, 2008 3:23:07 am BST 
 Report this message to moderators

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.

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « changin format of date in excel » in :
Change the date of birth on Facebook ShowChange the date of birth on Facebook Changing the date of birth is something that is not possible directly from the "General information" tab from Facebook account. The is one alternative though, click here Enter the email address...
[Samsung player one]Changing the date and time Show[Samsung player one]Changing the date and time Issue How to change the date and time on the samsung one player because the phone is set in 1980 by default. Solution To set the time and date, go to Settings/time and date/,...
[Excel]changing cell formula to text Show[Excel]changing cell formula to text Issue Solution Notes Issue Consider that I have: In cell A4, it contains a formula =Sum(A1:B3)+A3/B2. How to extract this formula in cell A6 as a string of text? That is ... I want cell A6...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Databases - Using forms ShowUsing forms In order to use databases, the user must be provided with an interface that allows him or her to view data based on certain criteria. There is a tool for this: forms. A form is an interface with components for displaying, entering, or...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

karina, on Oct 8, 2008 5:56:45 am BST
  • +7

Go to Format Cells, click on Number Tab and then select Custom and type dd/mm/yy under Type:

   
Reply to karina

2

drizn, on Oct 8, 2008 10:50:34 pm BST
  • +1

Tried that but it isnt working.

   
Reply to drizn

3

chomps, on Nov 19, 2008 9:19:04 am GMT
  • +10

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....

   
Reply to chomps

4

Pancho, on Dec 22, 2008 10:18:02 pm GMT
  • +5

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.

   
Reply to Pancho

6

hr intern, on Jan 27, 2009 5:09:30 pm GMT
  • +2

When I click 'Data', I don't see 'next' and i can't find any 'mdy' or 'dmy'

i have excel 2003

   
Reply to hr intern

7

lindsay, on Jan 29, 2009 6:19:50 pm GMT
  • +1

I think he meant to say Data, Text to Columns, then Next, Next, Date: MDY.

   
Reply to lindsay

18

 Pete, on Nov 17, 2009 9:02:45 am GMT

Thank you Lindsay and Pancho
This sorted the problem

On a sligtly different tack, why did the help not list this solution?

Regards
Pete

   
Reply to Pete

5

rahul, on Dec 23, 2008 6:21:53 pm GMT

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

   
Reply to rahul

8

SID, on Jan 30, 2009 12:50:34 pm GMT
  • +4

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.

   
Reply to SID

9

confy, on Feb 18, 2009 3:23:48 pm GMT
  • +1

Check the regional settings of your computer

   
Reply to confy

10

khoi, on Mar 2, 2009 3:20:41 pm GMT
  • +6

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.

   
Reply to khoi

11

bond, on Mar 11, 2009 6:21:12 pm GMT

Great help. Quick and easy. Thanks.

   
Reply to bond

12

Z, on Mar 31, 2009 1:10:09 pm BST
  • +5

If I want to remove everything but the year, is that possible?

Also, if I want to create a second column that lists whether that year is "even" or "odd," is there a formula for that? Thanks!

   
Reply to Z

13

EEKAY, on Apr 18, 2009 7:53:01 am BST
  • +1

Thanks a lot ... it was very very useful. I was fighting with this for the past 2 hrs. Within few seconds it is resolved

   
Reply to EEKAY

14

michele, on May 16, 2009 8:29:02 pm BST
  • +8

I need to remove the time from colume a it has both date and time and the time is unecessary??

   
Reply to michele

15

ROCK, on Jun 22, 2009 6:42:51 am BST
  • +2

Thanks a lot, this one is very helpful.

   
Reply to ROCK

16

Fatima, on Jun 26, 2009 7:55:49 am BST

Thank You--At least my issue is resolved.

   
Reply to Fatima

17

epayne, on Oct 9, 2009 5:15:36 am BST

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

   
Reply to epayne
Ask your question Reply
They need your help