Using the "Created Date" field in formulas

KGetchell

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

Paul Newcome

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

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.

Paul Newcome

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))))

KGetchell

Thank you, Paul!

I'm now getting the #INVALID VALUE response. I did "relink" each field reference but that only changed the error from #UNPARSEABLE to #INVALID VALUE.

Thanks!

Paul Newcome

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.

KGetchell

Aha, Data Type changed, Formula Success!

Thank you, Paul!

Paul Newcome

Happy to help. 👍️