Date in formula

I have the following formula set to add a date to a column once information has been added to the selected columns on the same row.

=IF(AND(ISBLANK([HCPCS/CPT/Supply Code]@row) = false, ISBLANK([Code Description]@row) = false, ISBLANK([Clinic Cost per Vial/ pre-filled syringe]@row) = false, ISBLANK([Vial Size]@row) = false, ISBLANK(Manufacturer@row) = false, ISBLANK([Desired Contact Date]@row) = false, ISBLANK([SharePoint Ticket #]@row) = false), TODAY(), "")

The problem is the formula updates the date every time someone saves the sheet. I want the date to be entered into the cell only once and not update after that as I want to know what date the information was entered as well as having an automation to send a message when the date is entered. With TODAY() in the formula the date updates and the automated message sends every time the sheet is saved. Is there a way to have the date added once and not changed after that?

Best Answer

  • Matt Lynn-PCG
    Matt Lynn-PCG ✭✭✭✭✭✭
    Answer ✓

    @Jennifer Lawson
    If you do an automation called "Record a date" then that automation would log the date in the cell when something occurs and that date would only update if that thing happened again or someone changed it. If you still need if() functionality then I'd say have a hidden helper column to record the date, then simplify this formula to choose between today() and that helper column.

    Certified Platinum Partner

    PrimeConsulting.com

Answers

  • Matt Lynn-PCG
    Matt Lynn-PCG ✭✭✭✭✭✭
    Answer ✓

    @Jennifer Lawson
    If you do an automation called "Record a date" then that automation would log the date in the cell when something occurs and that date would only update if that thing happened again or someone changed it. If you still need if() functionality then I'd say have a hidden helper column to record the date, then simplify this formula to choose between today() and that helper column.

    Certified Platinum Partner

    PrimeConsulting.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!