How to have a date formula based off another date formula?

Hello,

My situation is I have a master list that has all my event IDs and event start dates. Every event has a separate "project plan" sheet based off a template. When you enter the Event ID on the project plan in [details]1, the event info, such as event start date, pulls from the master sheet using index match.

Part of the project plan is having baseline milestone dates. They use a formula to calculate the baseline date a step should be completed based on the Event start date and a project plan column [target days to ad board].

My issue is the baseline dates are giving me "#invalid data type" and I believe it's because the formula is based off the value of another formula.


Project plan fields:

Event Start Date ([Details]5 = index match to master sheet

Baseline Date [baseline] = workday([details]5, - [target days to event]@row)


Any ideas? Thank in advance!!



Tags:

Answers

  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭

    Hi @Julia C Peterson


    Your thought about why you are getting this error is correct. The value that is listed in [Details]5 appears to be a text value. The formula only works when referencing a date in a date column.


    I have used hidden date columns, added the date to the actual date column and have made use of using the parent roll up functionality in order to be able to capture my start date in an actual date field so that I can work with formulas needed.


    All the best,

    Sandra

  • @Julia C Peterson I don't have an answer for you but I do have a question. I am new to Smartsheet and am interested creating a master list for all my events like the one you have there. Did you pull that from a template or are you willing to share your sheet with me as a template? ewells@sanantoniosports.org Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!