Trouble with IF statement and date format conversion

Meredith Rhodes

Hi all,

I'm having a formula issue that I need human support for. [AI is failing to translate it properly].

Here's my scenario (I'm so close!):

I have an orange text column [Delayed Result Date] that sometimes contains MM/YYYY format and other times MM/DD/YYYY format.

I need to make it a (yellow) date column [Delayed Result Date Formula]. If the text column 'date' already contains the DD, then I want that exact date in the date column. If the text column is MM/YYYY, then I want the last day of the month to be in the date column.

Here's my current formula:

=IF(LEN([Delayed Result Date]@row) = 10, DATE(VALUE(RIGHT([Delayed Result Date]@row, 4)), VALUE(LEFT([Delayed Result Date]@row, 2)), VALUE(MID([Delayed Result Date]@row, 4, 2))), IF(LEN([Delayed Result Date]@row) = 7, DATE(VALUE(RIGHT([Delayed Result Date]@row, 4)), VALUE(LEFT([Delayed Result Date]@row, 2)) + 1, 1) - 1))

It is failing to translate 12/2016, but it works in all other cases.

Wondering if anyone can suggest a change to my formula that would resolve this #invalid value error.

Thank you!
Meredith

Paul Newcome

It is because 12 + 1 is 13, and there is no 13th month.

Try this instead:

=IF(LEN([Delayed Result Date]@row) = 10, DATE(VALUE(RIGHT([Delayed Result Date]@row, 4)), VALUE(LEFT([Delayed Result Date]@row, 2)), VALUE(MID([Delayed Result Date]@row, 4, 2))), IF(LEN([Delayed Result Date]@row) = 7, DATE(VALUE(RIGHT([Delayed Result Date]@row, 4)) + IF(LEFT([Delayed Result Date]@row, 2) = "12", 1, 0), VALUE(LEFT([Delayed Result Date]@row, 2)) + IF(LEFT([Delayed Result Date]@row, 2) = "12", -11, 1), 1) - 1))

Meredith Rhodes

Thank you, as always, @Paul Newcome!

I thought maybe it was a December issue.

I've used a version of this formula elsewhere on my sheet - successfully with December dates (MM/YYYY to the last day of the month):

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

But I honestly can't interpret this in plain language. I think you were the wizard who solved this for me.

I appreciate your help :)

Meredith

Paul Newcome

Happy to help. 👍️

That formula in your last comment is accounting for moving from December to January using the IFERROR.

=IFERROR(DATE(yyyy, mm + 1, 1), DATE(yyyy, 1, 1))