Stop counting months when a completion date has been met
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...
Comments
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!