is it Possible to build a Date using Strings(Text Columns)

Eduardo Fernández

For example, let's say I have a text Column Called "Day" and another one called "Month"

I was trying to accomplish something like this:

=DATE(YEAR(TODAY()), MONTH(DATE(YEAR(TODAY()), Day@row + Month@row)), DAY(DATE(YEAR(TODAY()), Day@row + Month@row)))

Context:

* I'm trying to create a Birthday Reminder, so I want to be able to build a date without the year using 2 different columns, is this possible, It currently

But if I try to build it manually entering the values Day and Month it does work:

=DATE(YEAR(TODAY()), MONTH(DATE(YEAR(TODAY()), 5, 9)), DAY(DATE(YEAR(TODAY()), 3, 3)))

any ideas?

Paul Newcome

It looks like the data in the Month and Day columns are text values. try this instead...

=DATE(YEAR(TODAY()), VALUE(Month@row)), VALUE(Day@row)))

Paul Newcome

Happy to help. 👍️

The reason the first worked when you removed the zero from the beginning is that leading zero converts it to a text value, but the DATE function requires a numerical value. That's why the VALUE function works with the leading zeros because it essentially strips the leading zero and converts the data into a numerical value.

Paul Newcome

To have the birthday in the current year, you would want something like this...

=DATE(YEAR(TODAY()), Month@row, Day@row)

Eduardo Fernández

Thank you for your answer, I think I had already tried that but it didn't work I tried again but same result:

I'm trying to do something like this but I get an #INVALID DATA TYPE error, any ideas why? The columns Month and Day are "Text/Number" type of columns

Paul Newcome

It looks like the data in the Month and Day columns are text values. try this instead...

=DATE(YEAR(TODAY()), VALUE(Month@row)), VALUE(Day@row)))

Eduardo Fernández

Thank you very much I got it to work with this approach:

* Going back to your first answer =DATE(YEAR(TODAY()), Month@row, Day@row)

** it does the trick if you remove the zero before numbers from 1 to 9, not sure why.

---------

I also tested your new fix using VALUE([row]) and it works, it seems to be more efficient so I'm gonna replace the old fix.

Here's the final formula:

=IF(AND(NOT(ISBLANK(Day@row)), NOT(ISBLANK(Month@row))), DATE(YEAR(TODAY()), VALUE(Month@row), VALUE(Day@row)))

Thanks again!

Paul Newcome

Happy to help. 👍️

The reason the first worked when you removed the zero from the beginning is that leading zero converts it to a text value, but the DATE function requires a numerical value. That's why the VALUE function works with the leading zeros because it essentially strips the leading zero and converts the data into a numerical value.