In need of human brain to resolve AI formula.
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
Best Answer

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
Answers

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

@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

Oh, don't forget to add the beginning IF statement
Help Article Resources
Categories
Check out the Formula Handbook template!