Using the "Created Date" field in formulas

Options

Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the "Created Date" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the two. A simple =[Invoiced Date (EP)]@row - Created@row formula is returning as #INVALIDOPERATION.

Is it possible to use fields with date/time combos in formulas? If so, does anyone have any guidance on how the formula should be setup?

Thank you in advance!

Kate


Best Answers

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

    The Created (date) field is not the issue in this instance. The problem is the data in your Invoiced Date column is actually text strings instead of date values.


    Try putting this into a date type column and using this new column in place of the Invoiced Date column in your original formula.

    =DATE(VALUE(MID([Invoiced Date (EP)]@row, FIND(" ", [Invoiced Date (EP]@row) - 4, 4)), VALUE(LEFT([Invoiced Date (EP)]@row, FIND("/", [Invoiced Date (EP)]@row) - 1)), VALUE(MID([Invoiced Date (EP)]@row, FIND("/", [Invoiced Date (EP)]@row) + 1, FIND("/", [Invoiced Date (EP)]@row, FIND("/", [Invoiced Date (EP)]@row) + 1) - (FIND("/", [Invoiced Date (EP)]@row) + 1))))

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

    Double check that you are putting it in a date type column. Can you also provide a screenshot of those same rows with the errors but also showing the Invoiced Date? It is working for me.



Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!