How to compute aging if date format is in text/number column?

Asnithaa
Asnithaa ✭✭✭
edited 03/06/24 in Formulas and Functions

Hi All,

Would like to ask what formula I can use in row 4 to compute the aging (in days) between Actual Completion Date and Due Date.

All dates format are in text/number.

I tried with below formula in actual excel and it's working, however I'm getting 'Invalid value' in Smartsheet.

Formula used in row 4:

=DATE(VALUE(2000 + RIGHT([Activity 1]3, 2)), VALUE(LEFT([Activity 1]3, 2)), VALUE(MID([Activity 1]3, FIND("/", [Activity 1]3) + 1, 2))) - DATE(VALUE(2000 + RIGHT([Activity 1]2, 2)), VALUE(LEFT([Activity 1]2, 2)), VALUE(MID([Activity 1]2, FIND("/", [Activity 1]2) + 1, 2)))

Thank you in advance.


Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @Asnithaa,

    You are getting the #Invalid value message because your formula is attempting to put a "date" value in a column that is not set to "date" type. The only way to make that scenario work would be to have another column, set as "date", using the formula.

    Note: I am only speaking to the error you are receiving, I have not looked into the formula itself to know if it is correct otherwise.

    Hope this helps,

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!