Gantt View Not Working with Fx dates

Options

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?


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Options

    @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?

    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
    Options

    @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.


    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
    Options

    @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.
    Genevieve P. Employee Admin
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!