Converting text to Date to populate Gant & Calendar App.

kaia2001

I have an Allocation sheet where I need the user to choose the month in the "Month" column and the 'Start' populates with the first date of the month and the "End" populates with the last date of the month so I can have a start and finish date to show the resource and Allocation on a Gant and Calendar app. I have tried to use =DATE(YEAR(Month@row), MONTH(Month@row), 1) and =DATE(YEAR(Month@row), MONTH(Month@row) + 1, 1) - 1

but they are formatted as dates. Is there any other way to accomplish this?

MCorbin

First... I highly recommend having a Cheat Sheet for Date Related info. Here's Mine: (it's referenced in the formula I'm about to give you)

I use this as a reference in a LOT of formulas, especially when I need to get to the last day of a month.

Now, for your sheet....

You can create a helper column that extracts the month number from your month dropdown:

The formula I used is:

=IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3)) = "DEC")))))))))))

From there, your formula for the Start Date:

=DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, 1)

And the formula for the End Date (which includes a cross sheet reference to my Date Cheat Sheet):

=DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, INDEX({Last Day}, MATCH([Month Number]@row, {Month No}, 0)))

Now... if you didn't want to have a helper column, you could technically include that formula inside the other formulas:

=DATE(VALUE("20" + RIGHT(Month@row, 2)), IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3) = "DEC", 12))))))))))))))), 1)

But frankly.... I don't recommend it.... I prefer to create helper columns and hide them....

kaia2001

Great! Thank you! Much appreciated!

Andrée Starå

@kaia2001

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

MCorbin

First... I highly recommend having a Cheat Sheet for Date Related info. Here's Mine: (it's referenced in the formula I'm about to give you)

I use this as a reference in a LOT of formulas, especially when I need to get to the last day of a month.

Now, for your sheet....

You can create a helper column that extracts the month number from your month dropdown:

The formula I used is:

=IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3)) = "DEC")))))))))))

From there, your formula for the Start Date:

=DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, 1)

And the formula for the End Date (which includes a cross sheet reference to my Date Cheat Sheet):

=DATE(VALUE("20" + RIGHT(Month@row, 2)), [Month Number]@row, INDEX({Last Day}, MATCH([Month Number]@row, {Month No}, 0)))

Now... if you didn't want to have a helper column, you could technically include that formula inside the other formulas:

=DATE(VALUE("20" + RIGHT(Month@row, 2)), IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "JAN", 1, IF(LEFT(Month@row, 3) = "FEB", 2, IF(LEFT(Month@row, 3) = "MAR", 3, IF(LEFT(Month@row, 3) = "APR", 4, IF(LEFT(Month@row, 3) = "MAY", 5, IF(LEFT(Month@row, 3) = "JUN", 6, IF(LEFT(Month@row, 3) = "JUL", 7, IF(LEFT(Month@row, 3) = "AUG", 8, IF(LEFT(Month@row, 3) = "SEP", 9, IF(LEFT(Month@row, 3) = "OCT", 10, IF(LEFT(Month@row, 3) = "NOV", 11, IF(LEFT(Month@row, 3) = "DEC", 12))))))))))))))), 1)

But frankly.... I don't recommend it.... I prefer to create helper columns and hide them....

kaia2001

Great! Thank you! Much appreciated!

kaia2001

@MCorbin Would you know why it isn't recognizing the columns as being date columns? When I attempt to see a Gant View of the months it says "The Sheet must contain 2 date columns"

Andrée Starå

Hi @kaia2001

I hope you're well and safe!

It's because they are set with Column Formulas. If you remove it, it should work.

Did it work?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

kaia2001

It worked! Thank you!!

Andrée Starå

@kaia2001

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!