In need of human brain to resolve AI formula.

Meredith Rhodes

I have a text column that says the following: 09/2022

I would like to turn this into a date for the last day of the month, like this: 09/30/2022

This is what the AI generated formula gave me:

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

I'm getting a #INVALID VALUE error

The column I'm attempting to populate is a date column.

I DO have a formula that will turn the 09/2022 into 09/20/22 (I'll paste it below), *which is close*, but I really need it to be the last day of the month as this is a legal due date I'm tracking.

=IFERROR(DATE(VALUE(RIGHT([Delayed Result Date]@row, 4)), VALUE(LEFT([Delayed Result Date]@row, 2)), VALUE(MID([Delayed Result Date]@row, FIND("/", [Delayed Result Date]@row) + 1, 2))), [Delayed Result Date]@row)

Can anyone either assist me with the AI generated formula or tweak the formula that is about 10 days off?

Thank you!

Meredith

Kelly Moore

@Meredith Rhodes

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

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)),.....)

Meredith Rhodes

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?

Meredith Rhodes

@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.

Kelly Moore

@Meredith Rhodes

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

Meredith Rhodes

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?

Meredith Rhodes

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

Kelly Moore

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

Meredith Rhodes

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.

Kelly Moore

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

Meredith Rhodes

This gives me 04/30/24.

Meredith Rhodes

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.

Meredith Rhodes

This is perplexing.

Kelly Moore

@Meredith Rhodes

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

Kelly Moore

@Meredith Rhodes

Oh, don't forget to add the beginning IF statement