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

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    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.

  • John Jonassen
    John Jonassen ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!