Calculations
Hello Smartsheet Users,
I have a project I am working on and I need to my calculation to look at two different columns. So here it is I have a column with Employee ID's and a Date Column. I need to a column to show a flag if the employee ID shows up more than once within 30 days. The calculation will look back at the original date for example: Employee 123456 logged in 10/2/2020 and then logged in again 10/15/2020 a Flag will then show up. I need some assistance, please!
Answers
-
Lisa, try this technique as an example:
3 columns: ID, Login Date, Flag (of type Checkbox)
The Flag column will contain this formula:
=IFERROR(IF(MIN(COLLECT([Login Date]:[Login Date], [Login Date]:[Login Date], >[Login Date]@row, ID:ID, ID@row)) - [Login Date]@row <= 30, true, false), false)
Should look something like this:
P.S. Don't forget to convert the formula to a column formula once you get it working. It's the newest and BEST new feature of Smartsheet.
I hope this helps.
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Try putting this in your symbol column that will show your flag.
=IF(COUNTIFS({Employee ID from the Log In Source Data Range}, [Emp ID]@row, {Log In Date Column from the Log In Source Data Range}, >=(TODAY() - 30), {Log In Date Column from the Log In Source Data Range}, <=(TODAY()))>1,1,0)
-
@Ramzi and @John Jonassen thank you both for the assistance. Unfortunately I didn't receive any results and I think it has to do with converting the formula to a column formula. I'm not exactly sure what that means.
should I start my column as a text/number when I put my formula in before changing to Checkbox? I do apologize just slightly confused.
-
@Ramzi I found this "Licensed sheet owners and sheet admins can now convert their cell level formulas to an entire column in Smartsheet. To get started, enter a formula in a cell, right click, and select “Convert to Column Formula” from the cell options menu. This will apply the formula to every cell in the column, uniformly" however I do not have the ability to do this. Is there an different option?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!