Using the "Created Date" field in formulas
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
-
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))))
-
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
-
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))))
-
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!
-
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.
-
Aha, Data Type changed, Formula Success!
Thank you, Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!