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)
Hello, I'm seeing this error for the first time as I expand some capability in an existing workflow. Here's how this section with the error works. Scenario: a user puts in a request on the form with a check box indicating they will later upload the required file attachment. Automation sends a request for Approval with…
I have a task that started in Sept and ends in Feb. I need to assign it to someone else, but do not want to lose the history of the first employee. How do I do this?
Starting a couple of days ago, I started getting 2 or more emails a day about my account being activated. I have a monthly subscription but have never received this type of email from Smartsheet. The email looks legitimate - the sender is "reply_to@Smartsheet .com" - except for maybe the greeting, which is "Dear Customer".…
