Changing format of date in Excel [Solved/Closed]
drizn - - Latest reply: Dnbaugh - Nov 9, 2017 at 02:46 PM
Related:
- Date format not changing in excel
- Change date format in excel - Best answers
- How to change date format in excel - Best answers
- Unable to change date format in Excel ✓ - Forum - Excel
- Date format not changing in excel 2007 ✓ - Forum - Excel
- Change date format dd.mm.yyyy to dd/mm/yyyy ✓ - Forum - Excel
- Excel date format won't change ✓ - Forum - Excel
- How To Change the Date Format in Excel - How-To - Excel
20/22 replies
Best answer
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.
A few words of thanks would be greatly appreciated. Add comment
has helped 2585 users this month
View all 8 comments
Report
i need to remove the time from colume a it has both date and time and the time is unecessary??
Report
thank you...
Report
Thanks so much!! Worked perfectly!!
Report
Thanks for this.
Report
thank you.. I have searched and searched for this answer. I could not get my pivot table to group because of this. too much data to retype. thank you
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.
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.
- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
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
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
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
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.
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.
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
Hope this helps
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.
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.
- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
Upload a sample work book with this problem at some share site like http://www.speedyshare.com/ and post link
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]
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]
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
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