In need of human brain to resolve AI formula.

Your date in the Delayed Result Date is not a real date - it is a textstring that looks like a date.

Change your current formula in the Delay Type to this:

=DATE(VALUE(RIGHT([Delay Type]@row, 4)), VALUE(MID([Delay Type]@row, FIND("/", [Delay Type]@row) - 2, 2)), 1)

This should fix the other column.

Kelly

All comments

Razetto

@Meredith Rhodes Hi, I have a way of doing it but it's a long formula; I'm sure someone will know of an easier one. For example, the formula below is for Sept thru Nov:

=IF(VALUE(LEFT([Delayed Result Date]@row, 2)) = "9", "09/30" + "/" + VALUE(RIGHT([Delayed Result Date]@row, 4)), IF(VALUE(LEFT([Delayed Result Date]@row, 2)) = "10", "10/31" + "/" + VALUE(RIGHT([Delayed Result Date]@row, 4)),IF(VALUE(LEFT([Delayed Result Date]@row, 2)) = "11", "11/30" + "/" + VALUE(RIGHT([Delayed Result Date]@row, 4)),.....)

Thanks, @Razetto...

There really are so many right ways ;)

I've been coached to create a formula that adds a month and subtracts a day - I think to ensure the occasional Feb 29 happens. It looks like the AI formula is trying to do this, but I'm not yet skilled enough in date formulas to figure out how to make it right.

Razetto

@Meredith Rhodes I think that the AI formula is assuming that Delayed Result Date is a date column and not text. Have you tried testing it using a date column?

@Razetto This could be.

If I force that column to be a date column, it makes the date the first of the month.

Hmmm... maybe this is it.

Try this

=DATE(YEAR([Delayed Result Date]@row), MONTH([Delayed Result Date]@row) + 1, 1) - 1

This forces the date to the first day of the next month, then subtracts one day from that - which is the last day of the month of your current month.

Will this work for you?

Kelly

UPDATE:

When I force my text column with 09/2022 to be a date column, it becomes 09/01/22.

When I ask AI to add one month to this and substract one day it gives me =DATE(YEAR([Delayed Result Date]@row), MONTH([Delayed Result Date]@row) + 1, DAY([Delayed Result Date]@row) - 1)

This has a #Invalid Data Type error.

Ideas?

@Kelly Moore - I'm getting an Invalid data type error. Do you know what the potential sources of this error are?

Hey @Meredith Rhodes

As suggested by @Razetto, have you verified that both your [Delayed Result Date]@row column and the column you are pasting this formula into are actually Date type columns. Check your column properties to verify.

Also, the formula shown you showed immediately above is not the formula I provided. Once your column properties are verified, please copy paste the formula below into the appropriate column.

DATE(YEAR([Delayed Result Date]@row), MONTH([Delayed Result Date]@row) + 1, 1) - 1

Does this work for you?

Kelly

Hi @Kelly Moore -

I can confirm that I have turned my source column into a date column and that I have copied your exact formula into the cell.

Still, Invalid Data Type.

I DO have a formula in the Delayed Result Date cell to extract only the numbers out of the cell next to it. Maybe this is the problem. I'll try a helper cell that copies the date.

Hey @Meredith Rhodes

As a test, in your formula column, please try this formula

=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)-1

What does this give you?

Kelly

This gives me 04/30/24.

The column with the date I'm referencing has this formula in it:

=IF(NOT(ISBLANK([Delay Type]@row)), MID([Delay Type]@row, FIND("Extension:", [Delay Type]@row) + 11, LEN([Delay Type]@row)), "")

To extract the numbers from the "Extension:" text.

So I think this must be the issue.

This is perplexing.

Your date in the Delayed Result Date is not a real date - it is a textstring that looks like a date.

Change your current formula in the Delay Type to this:

=DATE(VALUE(RIGHT([Delay Type]@row, 4)), VALUE(MID([Delay Type]@row, FIND("/", [Delay Type]@row) - 2, 2)), 1)

This should fix the other column.

Kelly