Getting status of a RYG with a Date Column

pbeahn40831

Greetings, 

Thank you for taking the time to read this. I am a newbie to both programming functions and to Smartsheet. I am working on a sheet where I require to report out the RYG Balls on the end of calendar months. 

I have two columns I need to create a function for- "Date of Update" and "Status Report."  When the Status is turned, the date column updates with the "today's date".

But now, going forward, I want to manage a monthly report where it shows the end of the Month day, followed by a count of the status of each color. In other words, (September = 3R, 5Y, 19G,) and (October = 4R, 2Y, 8G,) and ... The report will appear on a Bargraph Dashboard widget monthly.

Since I am new with this, I have looked at COUNTIFS, VLOOKUP, IF/AND, but am missing something. 

 

Your help would be appreciated.

Thanks!

 

 

eric.o

Hello,

Happy to help! I've addressed your desires in the order received.

 

1. If you desire that when "Status Report." is changed the "Date of Update" will be changed to today's date, you may be able to achieve this utilizing a Modified By column which is further outlined here: https://help.smartsheet.com/articles/1964567-system-columns

Note: The Modified Date alters to the date of the rows last modification. Which may not be ideal.  Currently, we don’t have a method to Timestamp date columns utilizing formulas but this will be considered as a possibility for future development.

 

2. If you'd like to utilize a Formula to count the status colors for each color based on month. You may want to create a total sheet containing a Date Column and two Text columns. In the Date column, you can set the End Dates for each month. In the first text column, you can list the Total Names for example, "Green Total", next row "Red Total" etc. In the third column, you can place the formula that will reference the desired sheet and produce the result. (This is how I've seen customers achieve this in the past). The formula would be similar to this:

 

=COUNTIFS([Status Range]2:[Status Range]5, "Green", [Date Range]2:[Date Range]5, MONTH(@cell) = MONTH([Month Day]1))

 

The formula would be slightly altered because instead of referencing the same sheet you'll be referencing another sheet. The formula above reads COUNTIF the status range [Column3]2:[Column3]5 equals the value "Green", COUNTIF the date range [Column4]2:[Column4]5 equals the value of the Month in the Month day cell. The  Month day cell will be the cell you set on the sheet with the Total values, the highlighted ranges above will be the referenced ranges on the other sheet. Once the formula is established you can copy the formula into the other two color totals and alter the "Green" value to "Red", "Blue", Yellow", etc depending on the row you're looking to total.

 

Please let us know if you have any questions on the above.

 

Cheers, 

Eric  

Smartsheet Support

pbeahn40831

Eric, 

I must say I am more afraid of programming than I was a short time ago, each new line brings new formulas. I am sure it will work its way out, but this is mind numbing at times.

Thanks for your help, I will get back to this after the holidays.

 

Happy holidays!

 

eric.o

Hello,

Formulas can be tricky but once you get an understanding of how they function it can be quite liberating. If you desire, I'd be happy to set aside some time with you for a quick phone call to discuss this particular formula in further detail. 

Cheers, 

Eric  

Smartsheet Support