Changing date format - August 2023 to 8/1/2023

liz123395 

You can convert the text-based month and year format to a date format in Excel by combining the month and year text strings with the day "1".

Here is a simple approach using a formula:

Assuming "August 2023" is in cell A1, you can use the following formula:

=DATEVALUE("1 " & LEFT(A1, FIND(" ", A1) - 1) & " " & MID(A1, FIND(" ", A1) + 1, LEN(A1) - FIND(" ", A1)))

This formula works by extracting the month and year from the text string and using the DATEVALUE function to convert it into a date format with the day set to "1".

Steps:

1. Assuming your dates are in column A:
2. Enter the formula in an adjacent cell or wherever you want the converted dates to appear.
3. Adjust the cell references accordingly.

This will give you a date format like "8/1/2023" based on the month and year mentioned in the text format.

Or you can create a custom date format to transform the text-based month and year into a date format with a fixed day (like the 1st).

Here is how to do it:

1. Select the cells containing the text-based dates.
2. Go to the "Format Cells" option:
• Right-click and select "Format Cells" or use the shortcut Ctrl + 1.
3. Select the "Custom" category.
4. Enter the custom date format:
• Use the type  d/m/yyyy in the Type box.

This does not actually change the content of the cell, but it displays the text as a date in the desired format.

If your text-based dates are in column A, the custom format should change "August 2023" to "8/1/2023".

This way, you retain the original text content while displaying it in a date-like format. Just remember, while it looks like a date, it's still a text entry.

See the attached file with the examples.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.