Couple of Time Formula Questions

GoGophers22

Background. I have 4 columns: Status, Status Date, IRB Submission Date, and IRB Elapsed Time.

1. 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.
2. 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()))

GoGophers22

Will try the record a date function. Any clue on 2nd question?

Mark Cronk

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

GoGophers22

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

Mark Cronk

Hi @GoGophers22 ,

Try:

=IF(AND(ISDATE([status]@row), ISDATE([IRB Submission Date]@row)), NETWORKDAYS([Status Date]@row, [IRB Submission Date]@row), "")

Work?

Mark