Forum Discussion
Changing date format
I am getting my dates in this format, 2020-09-15 and would like to convert to 09/15/2020, is there a way to do that. I tried all options I have under custom format, but nothing worked. I have attached my spreadsheet for you to view. The dates are in column N and O, StartDate labelled LaborDate. Thanks
8 Replies
Select column, Data->Text to Columns
On third step
Finish, dates will be as dates in your locale format.
Thanks, that seemed to be the easiest, I had tried before but was selecting other in the second step, not tab.
Thanks so much.
shetzel , you are welcome
shetzel ,,,
I've examined the attached sheet,, and got the basic mistake probably you have committed,,, since Date in column N & O are Left Aligned,, mean Excel considered as TEXT,,, perhaps initially Cell Format was TEXT, and you have entered the Dates,, the tried to apply the required Date Format,, and because cell are TEXT,, so that Excel has rejected the new Date Format.
The possible solution is re-write the Date in cells,, but are many so, I would like to suggest simple VBA Macro.
Sub test( )
Application.ActiveWorkbook.Worksheets("Weekly Data").Range("N3:N322") = Format(Date, "mm/dd/yyyy")
Application.ActiveWorkbook.Worksheets("Weekly Data").Range("O3:O322") = Format(Date, "mm/dd/yyyy")End Sub
N. B. Check the attached Workbook.
- If I may add ...
Create a custom date format
Format a date the way you want
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019
If you want to use a format that isn’t in the Type box, you can create your own. The easiest way to do this is to start from a format this is close to what you want.
Select the cells you want to format.
Press CTRL+1.
In the Format Cells box, click the Number tab.
In the Category list, click Date, and then choose a date format you want in Type. You can adjust this format in the last step below.
Go back to the Category list, and choose Custom. Under Type, you’ll see the format code for the date format you chose in the previous step. The built-in date format can’t be changed, so don’t worry about messing it up. The changes you make will only apply to the custom format you’re creating.
In the Type box, make the changes you want using code from the table below.
Nikolino
I know I don't know anything (Socrates)
- This will not work, since date are Formatted as TEXT,, check my post,,, suggested MACRO.
You are absolutely right, once again, has shown that first read and then write, can work wonders.
Convert dates stored as text to dates
Thanks for the hint
Nikolino
I know I don't know anything (Socrates)
shetzel The dates were in fact texts and could thus not be reformatted. I used "text-to-column" to convert the texts to dates in a YMD format. Then, I custom formatted it to "mm/dd/yyyy". See attached.
