谷歌表格中文本日期转MM/DD/YY数字格式技术问询

If you’ve got text dates like Monday, April 30, 2018 in your Google Sheet and need to convert them to a numeric MM/DD/YY date format (similar to Excel’s DATE() approach), here are two reliable solutions:

1. Quick & Simple: Use DATEVALUE() #

Google Sheets’ DATEVALUE() function can directly parse this long-form text date into a numeric date value. Here’s how to use it:

• In an empty cell (e.g., C2, assuming your text date is in B2), enter the formula:

  =DATEVALUE(B2)
  

• At first, the result might show as a raw numeric value (like 43210). To display it as MM/DD/YY:
  1. Select the cell(s) with the numeric date
  2. Go to Format > Number > Custom date and time
  3. Choose the MM/DD/YY template (or type it manually into the input box)
  4. Click Apply

This works for most region settings since Google Sheets recognizes full weekday, month name, day, and year formats.

2. Manual Parsing with DATE() (For Edge Cases) #

If DATEVALUE() fails due to region-specific date recognition issues, you can build the date manually by extracting year, month, and day components. Use this formula in C2:

=DATE(
  RIGHT(B2, 4),  // Extracts the 4-digit year from the end of the text
  MONTH(DATEVALUE(MID(B2, FIND(",", B2)+2, FIND(" ", B2, FIND(",", B2)+2)-FIND(",", B2)-2)&" 1")),  // Converts month name to numeric month
  LEFT(RIGHT(B2, 8), 2)  // Extracts the 2-digit day from the text
)

Formula Breakdown: #

• RIGHT(B2,4): Grabs the last 4 characters (e.g., 2018 from the sample date)
• MID(...): Pulls the month name (e.g., April) from between the first comma and the following space
• DATEVALUE(...)&" 1": Converts the month name to a valid dummy date (e.g., April 1) so MONTH() can return its numeric equivalent (4)
• LEFT(RIGHT(B2,8),2): Extracts the day value (e.g., 30) from the substring starting 8 characters from the end

After entering this formula, apply the same MM/DD/YY cell formatting as described in the first method to get your desired display.

Example Result #

For the text date Monday, April 30, 2018, both methods will produce a numeric date that displays as 04/30/18 when formatted correctly.

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