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 working through the writing formulas optional activity and in the 'Nested IF function - Progress Column' task it directs you to put in the following formula '=IF([% Complete]@row = 0, “Empty”)'. However, when I put this formula into the spreadsheet, '04a_Marketing Request Tracker - Formulas', I get an…
Currently Triggering recurring workflows are only supported at the top of each hour. For instance 10:00, or 11:00, etc. Would like the ability to set 10:30 or 10:45. Perhaps the inteface can stay hourly so it doesnt get huge, but have the ability to manually edit the time directly to any custom time.
I have an automation that has a condition where I am looking at the column "LEA Interco Supporting Site(s)" and trying to find columns that have the value "Fort Loramie" in them. This works well when "Fort Loramie" is the first item listed in the cell, but it doesn't work when "Fort Loramie" is the second or third value in…
