Calculations

Lisa Coleman

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!

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

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)

Lisa Coleman

@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.

Lisa Coleman

@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?