Changing date format

[Scott Hetzel]

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

[#2]

@Scott Hetzel 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.

[#3]

@Scott Hetzel 

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

https://support.microsoft.com/en-gb/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db...

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.

1. Select the cells you want to format.

2. Press CTRL+1.

3. In the Format Cells box, click the Number tab.

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

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

6. In the Type box, make the changes you want using code from the table below.

   Nikolino

   I know I don't know anything (Socrates)

[#4]

@Scott Hetzel ,,,

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.

[#5]

This will not work, since date are Formatted as TEXT,, check my post,,, suggested MACRO.

[#6]

@Rajesh_Sinha 

You are absolutely right, once again, has shown that first read and then write, can work wonders.

Convert dates stored as text to dates

https://support.microsoft.com/en-gb/office/convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-...

Thanks for the hint

Nikolino

I know I don't know anything (Socrates)

[#7]

@Scott Hetzel 

Select column, Data->Text to Columns

On third step

Finish, dates will be as dates in your locale format.

[#8]

@Sergei Baklan 

Thanks, that seemed to be the easiest, I had tried before but was selecting other in the second step, not tab.

Thanks so much.

[#9]

@Scott Hetzel , you are welcome