如何利用公式将美式日期转换为英式格式并合并重复日期？

Absolutely, you’ve got several solid formula options to handle this—whether you’re using Excel, Google Sheets, or another spreadsheet tool. Let’s walk through the solutions tailored to common scenarios:

1. Convert a Single American Date to British Format #

If your date is already recognized as a date value (not plain text) in your spreadsheet:

• Excel/Google Sheets: Use the TEXT function to reformat it:

  =TEXT(A1, "dd/mm/yyyy")
  

  Replace A1 with your cell containing the American date. This will output something like 29/06/2019 for 6/29/2019.

If your date is stored as plain text (e.g., "6/29/2019" instead of a date value):

• First convert it to a date value with DATEVALUE, then reformat:

  =TEXT(DATEVALUE(A1), "dd/mm/yyyy")
  

  Note: If your spreadsheet’s regional settings are set to British, DATEVALUE might misinterpret the text date. Use this formula instead to explicitly split month/day/year:

  =TEXT(DATE(RIGHT(A1,4), LEFT(A1,FIND("/",A1)-1), MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)), "dd/mm/yyyy")
  

2. Remove Duplicates & Combine into a Single String #

To take a range of dates (like your 6/29/2019, 07/01/2019, 6/29/2019, 07/01/2019), remove duplicates, and join them with &:

For Excel 365/Google Sheets (modern versions with dynamic arrays): #

Use UNIQUE to get distinct dates, then TEXTJOIN to concatenate them in British format:

=TEXTJOIN(" & ", TRUE, TEXT(UNIQUE(A1:A4), "dd/mm/yyyy"))

• UNIQUE(A1:A4) extracts only the unique dates from your range
• TEXT(..., "dd/mm/yyyy") converts each unique date to British format
• TEXTJOIN(" & ", TRUE, ...) joins the formatted dates with &, ignoring any empty cells

For older Excel versions (no UNIQUE function): #

Use an array formula (enter with Ctrl+Shift+Enter instead of just Enter) along with INDEX and MATCH to first get unique values, then join them:

=TEXTJOIN(" & ", TRUE, TEXT(INDEX(A1:A4, MATCH(0, COUNTIF($C$1:C1, A1:A4), 0)), "dd/mm/yyyy"))

Note: Enter this in a new cell (e.g., C2) and drag down until you get empty results, then combine those cells if needed—or use a helper column to list unique dates first, then join them.

Let me know if you’re working with a different tool (like LibreOffice Calc) and I can adjust the formula to fit!

内容的提问来源于stack exchange，提问作者WayneP