Formatting Dates

I received a spreadsheet today and the person who send it to me entered the dates in this format: 6.9.24

Excel doesn't recognize this as a date so I cannot reformat the cells or sort the range by date.

Any idea how to write a formula that could change the format to "6-9-2024"? I've attached a sample spreadsheet.

Thank you!

You can use this formula in B2:

=DATE(20&RIGHT(A2,2),LEFT(A2,FIND(".",A2)-1),SUBSTITUTE(MID(A2,FIND(".",A2)+1,2),".",""))

Format as a date in the style you prefer, then copy down as required.

Hope this helps.

Pete

Are you still using Excel 2003?
It limits the the solutions we could offer.

To convert the date format "6.9.24" to "6-9-2024" in Excel, you can use a combination of text functions like LEFT, MID, and RIGHT. Here's a formula you can use:

=IFERROR(DATE(2000+RIGHT(A1,2),MID(A1,3,FIND(".",A1)-3),LEFT(A1,FIND(".",A1)-1)),"Invalid Date")

Here's a breakdown of how the formula works:

• LEFT(A1,FIND(".",A1)-1): This extracts the day part of the date before the first dot.
• MID(A1,3,FIND(".",A1)-3): This extracts the month part of the date between the first and second dots.
• RIGHT(A1,2): This extracts the year part of the date after the second dot. We assume that the year is in the format of YY, so we add 2000 to convert it to YYYY format.
• DATE(year, month, day): This function constructs a date using the extracted day, month, and year parts.
• IFERROR(...,"Invalid Date"): This handles cases where the date format is not valid.

You can place this formula in a separate column and drag it down to apply to all the dates in your spreadsheet. Once you have the converted dates, you can format them as "dd-mm-yyyy" to display them in the desired format.

Please note that this formula assumes that the day, month, and year parts of the date are separated by dots. If your actual data has a different separator or format, you may need to adjust the formula accordingly.

No formulas
Data-->Text to Columns-->Next-->Next-->Date-->MDY-->Finish