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)
I'm receiving this message every time I try to open my sheets. I'm the only admin user. I have already performed proper troubleshooting, such as clearing the cache, logging in incognito, and logging out and back in, but the issue remains unresolved. It's been a few days, and I'm unable to track our projects and this…
I’ve created an automation that generates a document and forwards it to the individual specified in a cell. The line item also has several additional attachments associated with it. Is there a way to include those attachments with the generated document?
I have an automation set up that copies a row to another sheet each day. The automation itself works correctly, but the problem is that it copies the formulas rather than the values, which causes the formulas to break and return errors. How can I configure it to copy only the values instead of the formulas?
