Gantt View Not Working with Fx dates

RhondaRhocks

Projects are entered by fiscal year and quarter throughout the company; however, when toggling to Gantt view it refusing "A sheet must contain at least two date columns to display the Gantt view." The property of the column is date.

I've created a workaround to copy value to two other columns but for usability ease, it would be great to have the two DATE columns work as such. Am I missing something?

Genevieve P.

Hey @RhondaRhocks

I can see that your two date columns are pulling in values through a formula; is it possible that the formula is presenting the information as text instead of Date?

Can you post screen captures of the two column properties (double click the column names) as well as the two formulas you're using, and where the data is coming from? The columns in the source sheet will need to be set up as Date columns as well.

Thanks,

Genevieve

RhondaRhocks

@Genevieve P. ,

Thank you for responding!!

Is there a way to easily wrap a text string as a date even though the result and column property is a date?

Fiscal years.PNG

Project Dates as Formulas.PNG

Upcoming Training Registration.PNG

Key Projects looks at an external sheet that defines the quarters and fiscal years where the first 4 columns are text the rest are date fields.

From Key Projects: IFERROR(INDEX(COLLECT({Quarter_asDate}, {FYshort}, [FY Start]@row, {Q1234}, [Q Start]@row), 1), "")

Having a similar issue where I extracted dates from a text string within multi-select column:

From Course Registration:

IF(AND(ClassCT@row > 1, CONTAINS("201", Course@row)), MID(Course@row, FIND("201:", Course@row, 1) + 5, FIND(CHAR(10), Course@row, FIND(CHAR(58), Course@row, 1) + 17) - (FIND(CHAR(45), Course@row, 1) + 1)), IF(AND(ClassCT@row = 1, CONTAINS("201", Course@row)), MID(Course@row, FIND("201:", Course@row, 1) + 5, 8), ""))

This formula DID activate a field "Upcoming Training" dates that looked at the 3 date columns to trigger several events. I was super stoked I was able to parse the dates from the multi-select dropdown and cried when it deactivated the Upcoming Training column. Once I used the above formula, it turned entire column to "#INVALID OPERATION. :(

IF(MAX(Orientation@row:[Culinary Credentials 201]@row) > TODAY(), MAX(Orientation@row:[Course 201]@row), "No Future Dates")

Please tell me it's possible. Fingers crossed.

RhondaRhocks

@Genevieve P.

I learned from @Kelly 2021 on How to Convert Text to Date Format on how to convert the text string to date. The other learning curve was the years had to be 4 digits. I also cheated a little by adding duplicate helper columns and probably could have written a similar formula in first 3 date columns but I'm going to hide these instead.

Future Dates Now Evaluate Max Date.PNG

The Upcoming Training column can now evaluate the 3 date fields but it does not behave like a date field because when I mouse over it doesn't show the day and date like the Course 201 cell does. BUT if I change the column property type to text the formula gives me an #INVALID COLUMN VALUE - so am assuming it's working.

Course 201: =IFERROR(DATE(VALUE(RIGHT([201]@row, 4)), VALUE(MID([201]@row, FIND("/", [201]@row) - 2, 2)), VALUE(MID([201]@row, FIND("/", [201]@row) + 1, 2))), "")

Upcoming Training: =IF(MAX([Orientation Date]@row:[CC201 DATE]@row) >= TODAY(), MAX([Orientation Date]@row:[CC201 DATE]@row), "No Future Dates")

Hope this helps someone else looking to convert text dates to dates to be used in Gantts and triggering events.

RhondaRhocks

@Genevieve P. ,

After spending many hours trying to Gantt, I finally found your post from a couple of years ago.

Converted the column formula to a cell formula and viola!

Genevieve P.

Hey @RhondaRhocks

My apologies for missing these comments! I'm so glad to hear that you found the DATE() function to turn your data into Date type of values, and that my other post helped to explain it should not be a Column Formula.

Sounds like you have it all sorted out now, well-done!!