Trying to use IF DATE and auto number together in a formula

I am trying to link a date column with the day of a plants life cycle. If a plant was "born' on June 7th (Date column), then I want the "Day of Life Cycle" to be "Day 1" and June 8th to automatically populate as Day 2 of the plants life cycle, and so on. See attached image. This way, I can enter multiple "Tasks" (itemize each task with a separate line) with the same date and it will automatically tell me the number of days that plant has been alive on the date the activity took place (Or will take place). I believe that "Day of the Life Cycle" will be the column where the formula resides.

Thanks in advance!

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try something like this...

    ="Day " + [Task Date]@row - PARENT([Task Date]@row) + 1

  • Hello and thank-you for this suggestion. I am having trouble getting it to work. I see that PARENT is in the equation, but I do not have a parent/child relationship with this one. There is one parent at the very top, but I am not intending to use that aspect. I need everything to start on the 4th row down where "Day 1" begins on June 7th.

    Hope that makes sense. Maybe you can think of another formula or help me understand the formula you provided a little bot more so I can understand how that will fit into my spreadsheet.


  • Actually, I figured out how to make it work. I am excluding the "Day" part and just making it a number. It works great that way. Thanks for your help again!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad you were able to find a working solution. Happy to help. 👍️