Sign in to join the conversation:
Hi,
I need would like to have reports where all the result are from "this month" or "previous month" but I don't see such filters in When? Option. Can someone help me with that?
you would need to add a helper column. That is a new column with an identifying formula that will post dependent on your criteria.
=if(month(@desiredcell)=month(today()),1,if(month(@desiredcell=month(today())-1,2)
Then sort/filter the criteria with 1 being the current month in your new column and 2 being the previous month.
That's great, but what happens in january at MONTH(TODAY))-1? There is no month 0... I ask because I'm trying to get a dashboard to show a running total of items completed in the last 12 months. Currently I'm having to go and manually update the dashboards (i.e. point to the new data). I want to automate!
Nevermind -
=IF(MONTH(TODAY()) - [Column19]30 > 0, MONTH(TODAY()) - [Column19]30, MONTH(TODAY()) - [Column19]30 + 12)
where column19 contains the number of months into the past. Still working on determinig year, but updating once a year isn't so bad.
A trick to getting around that is using dates instead of months.
=date(2019, 1, 1) - 1
would return 12/31/2018
so you could use the month formula on the date
=month(date(2019,1,1)-1)
would return a 12
You can use this to fully automate your equation, though it will make it significantly more complex/long
=month(date(year(today()), month(today()),1) -1)
The formula would look something like this: (untested)
if(netdays(@desiredcell,today())>360,"",
if(month(@desiredcell)=month(today()),1,
if(month(@desiredcell)=month(date(2000,month(today()),1)-1),2,
if(month(@desiredcell)=month(date(2000,month(today()),1)-2),3,
if(month(@desiredcell)=month(date(2000,month(today()),1)-3),4,
if(month(@desiredcell)=month(date(2000,month(today()),1)-4),5,
if(month(@desiredcell)=month(date(2000,month(today()),1)-5),6,
if(month(@desiredcell)=month(date(2000,month(today()),1)-6),7,
if(month(@desiredcell)=month(date(2000,month(today()),1)-7),8,
if(month(@desiredcell)=month(date(2000,month(today()),1)-8),9,
if(month(@desiredcell)=month(date(2000,month(today()),1)-9),10,
if(month(@desiredcell)=month(date(2000,month(today()),1)-10),11,
if(month(@desiredcell)=month(date(2000,month(today()),1)-11),12,""
or collapsed:
if(netdays(@desiredcell,today())>360,"",if(month(@desiredcell)=month(today()),1,if(month(@desiredcell)=month(date(2000,month(today()),1)-1),2,if(month(@desiredcell)=month(date(2000,month(today()),1)-2),3,if(month(@desiredcell)=month(date(2000,month(today()),1)-3),4,if(month(@desiredcell)=month(date(2000,month(today()),1)-4),5,if(month(@desiredcell)=month(date(2000,month(today()),1)-5),6,if(month(@desiredcell)=month(date(2000,month(today()),1)-6),7,if(month(@desiredcell)=month(date(2000,month(today()),1)-7),8,if(month(@desiredcell)=month(date(2000,month(today()),1)-8),9,if(month(@desiredcell)=month(date(2000,month(today()),1)-9),10,if(month(@desiredcell)=month(date(2000,month(today()),1)-10),11,if(month(@desiredcell)=month(date(2000,month(today()),1)-11),12,""
You can find the previous months number by using =MONTH(TODAY() - DAY(TODAY()))
so this will tell you if the test cell is in the previous month. if(MONTH(TODAY() - DAY(TODAY())) = MONTH([Ship Scheduled]@row), 1, 0)
I used the following formula in a checkbox column to see if the date in my "Ship Scheduled" column is in the previous month. I added the 90 day limit to the first part of the AND statement to make sure that I am not getting false positives for dates more than 90 days in the past (e.g. 2,3,4 years ago, but the correct month)
=IF(AND(TODAY() - [Ship Scheduled]@row < 90, MONTH(TODAY() - DAY(TODAY())) = MONTH([Ship Scheduled]@row)), 1, 0)
Hi everyone, I'm the owner of worksheet/workspace and am currently unable to reliably remove user access through the "Manage Access" panel. This issue just started occurring recently. The Problem: When I click the "X" next to a user's name to remove them, it works for the first user. For any subsequent user, the "X"…
I'm trying to move data from two grids (Grid #1 and Grid #2) using workflows (When rows are changed) to archive grids when a cell is ONLY updated on Grid #1. Here is my setup and what the issue I'm having… Grid #1 has the column that is being updated. Workflow setup on Grid #1 to move the row when the cell is changed to a…
Helloe Everyone! I’m trying to reference another sheet in Smartsheet, but I’ve noticed that sometimes it loads quickly, while other times it takes a very long time to display the list. Does anyone know why this happens? Is there a way to reduce how often this issue occurs? Today, I can’t get anything to load at all. 😑
