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

Meredith Rhodes

@Paul Newcome - slightly different date formula issue that perhaps you can assist with.

I have a text field with date looking numbers that I need to turn into dates [ 01/2021 or 01/21/21 ] - I have successfully done this with the following formula - telling it to do different things if there are 7 (the month and year) or 8 characters (month, day, year):

=IF(LEN([Primary Completion Date CT.gov]@row) = 8, DATE(VALUE(RIGHT([Primary Completion Date CT.gov]@row, 2)), VALUE(LEFT([Primary Completion Date CT.gov]@row, 2)), VALUE(MID([Primary Completion Date CT.gov]@row, 4, 2))), IF(LEN([Primary Completion Date CT.gov]@row) = 7, DATE(VALUE(RIGHT([Primary Completion Date CT.gov]@row, 4)) + IF(LEFT([Primary Completion Date CT.gov]@row, 2) = "12", 1, 0), VALUE(LEFT([Primary Completion Date CT.gov]@row, 2)) + IF(LEFT([Primary Completion Date CT.gov]@row, 2) = "12", -11, 1), 1) - 1))

Problem is - it's returning the date 01/21/21 to be the year 1921, which is a problem ;)

Is there an easy way to modify my formula to add "20" as the first two digits of the year?

Thank you!
Meredith

Paul Newcome

@Meredith Rhodes In each place that you formulate the year, you would "add" "20" to the start of it inside of the value function. For example:

VALUE("20" + RIGHT([Column Name]@row, 2))

Meredith Rhodes

If I read this correctly, then for

01/21/21

=IF(LEN([Primary Completion Date CT.gov]@row) = 8, "20"+DATE(VALUE(RIGHT([Primary Completion Date CT.gov]@row, 2)), VALUE(LEFT([Primary Completion Date CT.gov]@row, 2)), VALUE(MID([Primary Completion Date CT.gov]@row, 4, 2))), IF(LEN([Primary Completion Date CT.gov]@row) = 7, DATE(VALUE(RIGHT([Primary Completion Date CT.gov]@row, 4)) + IF(LEFT([Primary Completion Date CT.gov]@row, 2) = "12", 1, 0), VALUE(LEFT([Primary Completion Date CT.gov]@row, 2)) + IF(LEFT([Primary Completion Date CT.gov]@row, 2) = "12", -11, 1), 1) - 1))

BUT it returns 2001/21/21 - which seems like the value on the left.

When I add it here:

=IF(LEN([Primary Completion Date CT.gov]@row) = 8, DATE(VALUE(RIGHT([Primary Completion Date CT.gov]@row, 2)), "20"+VALUE(LEFT([Primary Completion Date CT.gov]@row, 2)), VALUE(MID([Primary Completion Date CT.gov]@row, 4, 2))), IF(LEN([Primary Completion Date CT.gov]@row) = 7, DATE(VALUE(RIGHT([Primary Completion Date CT.gov]@row, 4)) + IF(LEFT([Primary Completion Date CT.gov]@row, 2) = "12", 1, 0), VALUE(LEFT([Primary Completion Date CT.gov]@row, 2)) + IF(LEFT([Primary Completion Date CT.gov]@row, 2) = "12", -11, 1), 1) - 1))

I break things entirely.

Meredith Rhodes

UPDATE: the column in question is formatted as a date column - but not all entries are 'dates'.

In other words, if a 1/21/21 is entered in this column and I hover over it, it reads January, 21, 2021. But if 1/2021 is entered in this column it does not recognize the date. (Which is why I'm making a formula to make them all dates).

Maybe I can make a logic statement that says "if this column is a date, return the date", if this column is not a date, then make it one. This might solve my problem. (rather than counting characters to solve it).

Meredith Rhodes

….OR make it a text column like I thought it was.

The more you know.

Paul Newcome

The text column would mean that nothing is considered a date and can't be used as a date in further calcs without added complexity to convert. If you leave it as a date type column, you can use

=IF(ISDATE([Date Column]@row), [Date Column]@row, DATE(VALUE(RIGHT([Date Column]@row, 4)), VALUE(LEFT([Date Column]@row, 2)), 1))