Couple of Time Formula Questions
Background. I have 4 columns: Status, Status Date, IRB Submission Date, and IRB Elapsed Time.
- We want whenever the Status changes (In Preparation, Submitted, Approved, Complete, Cancelled) to record the date. I thought this formula would work: =IF(Status@row = "Approved", TODAY(), IF(Status@row = "Complete", TODAY(), IF(Status@row = "In Preparation", TODAY(), IF(Status@row = "Submitted", TODAY(), IF(Status@row = "Cancelled", TODAY()))))) It does, BUT then when I go back to the sheet it, of course, refreshes to todays date.
- I am also trying to figure out the elapsed business day time of when a project is submitted and when it is accepted by the IRB. I created the column IRB Elapsed Time (not a very good name, but whatever). I tried out the automated record date workflow, that should hopefully record the date from when the Status changes to Submitted in the IRB Submission Date. Now to try and calculate the elapsed time between the IRB Submission Date date and the Status column (Accepted) in the IRB Elapsed Time column. Any clues? Am I making this harder than it needs to be? I tried doing this formula: =NETWORKDAYS([Status]@row, IF(ISDATE([IRB Submission Date]@row), ([IRB Submission Date]@row, TODAY()))
Answers
-
Will try the record a date function. Any clue on 2nd question?
-
Hi @GoGophers22 ,
You were on the right track. Tey:
=IF(ISDATE([IRB Submission Date]@row), NETWORKDAYS([Status]@row, ([IRB Submission Date]@row, "")
The record date automation is a great solution for your first issue.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Record date automation worked beautifully!
I'm still struggling with the formula unfortunately. I think maybe it has to do with the columns?
My columns are: Status (approved, In prep, complete, submitted and cancelled), Status Date, IRB Submission Date and IRB Elapsed Time.
I am trying to figure out the network days from the Status Date to the IRB Submission Date. The goal is to see on average how long it is from when a project is submitted to when it is approved. I don't know if I can make the formula run ONLY when the status changes to Approved. I don't know if that is possible, but it would be amazing. If not, I think I would need to add in the column IRB approval date to calculate the difference. Thoughts? Otherwise it will pull the status date even in its 'in prep' and calculate the difference in IRB submission time.
I tried the above formula and it didn't work:
=IF(ISDATE([IRB Submission Date]@row), NETWORKDAYS([Status]@row, ([IRB Submission Date]@row, "")
Any other thoughts? I know this is a hot mess.
Thanks so much!!!
-
Hi @GoGophers22 ,
Try:
=IF(AND(ISDATE([status]@row), ISDATE([IRB Submission Date]@row)), NETWORKDAYS([Status Date]@row, [IRB Submission Date]@row), "")
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!