Can you answer this question using AI?

Miscellaneous and Personal Stuff I Must Share
1

Continuing the discussion from Excel formula question:

The question in the original thread is:

“The headers are “Sum of 1/31/2024” “Sum of 2/29/2024” etc. Is there a formula I can put in another row that just pulls the date from those cells?”

If you put that into an AI program (ChatGPT/Gemini/CoPilot/whatever) can you get a correct answer? Which are closest? Close enough to lead you to the right answer or are they way off?

Can the question be asked a bit differently to get a better answer?

2

I asked ChatGPT this question, just restating the problem a little more explicitly:

“I have an Excel spreadsheet which has cells containing strings of the form “Sum of mm/dd/yyyy” where “mm/dd/yyyy” is a date. The “mm” part and/or the “dd” part may be one or two digits. What formula can I use to extract the date into another cell?”

It gave this answer, which similar to but more complicated than Pasta’s answer in the original thread. The use of TEXT() to format the result goes a little beyond what was asked.

You can use a combination of Excel functions to extract the date from a string like “Sum of mm/dd/yyyy”. Assuming the string is in cell A1, you can use the following formula:

=TEXT(MID(A1, FIND("of", A1) + 3, LEN(A1)), "mm/dd/yyyy")

Explanation:

  • FIND("of", A1) + 3: This finds the position of “of” and skips past it (3 characters) to get to the start of the date.
  • MID(A1, FIND("of", A1) + 3, LEN(A1)): Extracts the portion of the string starting at the position after “of”.
  • TEXT(..., "mm/dd/yyyy"): Formats the extracted date to ensure it’s in the correct “mm/dd/yyyy” format.

If your system uses a different date format (like “dd/mm/yyyy”), adjust the date format accordingly.

3

Your re-phrasing seems to have made it a text field instead of a date field. Is that better?

Here is what Copilot (Microsoft AI) gave to the question in the OP:

Yes, you can use a combination of Excel functions to extract the date from those header cells. Assuming your headers are in row 1, you can use the following formula to pull the date:

=DATE(RIGHT(A1,4), LEFT(MID(A1, 6, FIND("/", MID(A1, 6, 10))-1), 2), LEFT(A1, FIND("/", A1)-1))

Here’s a breakdown of what this formula does:

  1. RIGHT(A1, 4) extracts the year from the header.
  2. MID(A1, 6, FIND("/", MID(A1, 6, 10))-1) extracts the month.
  3. LEFT(A1, FIND("/", A1)-1) extracts the day.

You can adjust the cell reference A1 to match the location of your header. Copy this formula to the cells where you want the dates to appear.