Stop counting months when a completion date has been met

Elizabeth Lynner

Hello,

I need to have a column that calculates time in a program from a start date freeze when the employee's status changes, it needs to keep counting as long as the employee is still "Active" in the program. I'm working with "Status Code,", "Enrollment Date," and "Date Licensed/Dropped" columns.

I have this column set up in a google sheet now but want to move over to Smartsheet. My current formula looks like this:

=if([Status Code]="Dropped",([Date Licensed/Dropped]-[Enrollment Date]/7/4,if([Status Code]="Graduated",([Date Licensed/Dropped]-[Enrollment Date]/7/4,datedif([Enrollment Date]-today(),"d")/7/4))

I know that I can't use the datedif formula in smartsheet so i'm not really sure how to make this go...

J. Craig Williams

I don't know what the "/7/4" portion of the Google Sheet formula does.

It looks like if either Dropped or Graduated, then you take the difference between the two dates.

Try this:

=IF(OR([Status Code]@row = "Dropped", [Status Code]@row = "Graduated"),[Date Licensed/Dropped]@row -[Enrollment Date]@row, [Enrollment Date]@row - TODAY())

Smartsheet needs both column and row to reference the cell. @row is "this row". You can replace with the row number if you are referencing a different row than the one the formula is on.

I hope this helps. I did not test it.

Craig