Need assistance with a formula
Hello,
I am using the below formula to count the number of rows where 140R was checked previous month. This formula was working perfectly fine until this month when the year changed. How can I fix this formula where previous month should be looked at December 2019 for January 2020, but it should look at January 2020 in February 2020?
=COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = MONTH(TODAY()) - 1)
Best Answers
-
DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1
The above will give you the last day of the previous month. If you pull the year and month from this date for the year and month for your calculations, you should be good to go.
=COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
-
I personally would use a slightly different approach, but that really is just personal preference. The only change I would actually suggest to your formula would be adding in the IFERROR portions in case there are issues in the Date column such as text entries or blanks. It isn't really NECESSARY, but it is an added level of security/accuracy.
IFERROR(YEAR(@cell), 0)
IFERROR(MONTH(@cell), 0)
=COUNTIFS([140R]:[140R], 1, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
-
Yes.
DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
will give you the first day of the current month.
When you subtract 1 from that, it will go to the last day of the previous month regardless of whether it is a different year or not.
DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1
Using this to generate the date for the MONTH or YEAR function will pull the month or year accordingly for the last day of the previous month no matter what the previous month is.
MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)
YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)
-
That's because the -1 portion is not subtracting a month. It is subtracting a single day from the first of the month which in turn gives you the last day of the previous month to pull the month number and year number from.
To go back 2 months, I would suggest changing -1 to -35. This takes the current date and goes back 35 days THEN references the month and year from that date.
Answers
-
1-1 is not 12 is your issue.
=COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = month(date(year(2000,month(today()),1)-1)
See if that works for you.
-
It didn't work. I received an "#INCORRECT ARGUMENT SET ERROR" after using that formula.
-
=COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = month(date(2000,month(today()),1)-1)
Give that a try. I made an error in the year part of the formula.
-
The formula works; however, I am receiving the result as 0 even though there's data there for the previous month.
-
I called Smartsheet support but they weren't able to figure out the formula as well. Scheduled a pro-desk session hoping they can assist with the formula. Thank you though for your help.
-
If you have blanks in any of the cells it could potentially pop an error. Give this a try:
=COUNTIFS([140R]:[140R], 1, Date:Date, iferror(YEAR(@cell),0) = YEAR(TODAY()), Date:Date, iferror(MONTH(@cell),0) = month(date(2000,month(today()),1)-1)
-
Both the formulas work; however, they return a value of "0" for some reason. There's rows there for the previous month, but the formula isn't counting them for some reason.
-
DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1
The above will give you the last day of the previous month. If you pull the year and month from this date for the year and month for your calculations, you should be good to go.
=COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
-
Hello Paul. That worked! Below is the formula I currently have which calculates the data for the current month. Do you think this should be revised as well?
=COUNTIFS([140R]:[140R], 1, Date:Date, YEAR(@cell) = YEAR(TODAY()), Date:Date, MONTH(@cell) = MONTH(TODAY()))
-
I personally would use a slightly different approach, but that really is just personal preference. The only change I would actually suggest to your formula would be adding in the IFERROR portions in case there are issues in the Date column such as text entries or blanks. It isn't really NECESSARY, but it is an added level of security/accuracy.
IFERROR(YEAR(@cell), 0)
IFERROR(MONTH(@cell), 0)
=COUNTIFS([140R]:[140R], 1, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
-
Sounds good. 1 more question Paul. The formula you mentioned below for the Previous Month would work perfectly fine once February starts? It should look at January of 2020, correct?
=COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
-
Yes.
DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
will give you the first day of the current month.
When you subtract 1 from that, it will go to the last day of the previous month regardless of whether it is a different year or not.
DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1
Using this to generate the date for the MONTH or YEAR function will pull the month or year accordingly for the last day of the previous month no matter what the previous month is.
MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)
YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)
-
Thank you @Paul Newcome and @L@123 for taking the time out and assisting me with this formula.
Best Regards,
Nikhil Chawla
-
@Nikhil Chawla Happy to help! 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that help may be found here.
-
@Paul Newcome Will the same formula not work if I try to get the counts for 2 months ago? I replaced "-1" with "-2" and that didn't work. The two months ago formula returns the same value as the Previous Month Formula.
Previous Month Formula
=COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))
Two Months Ago Formula
=COUNTIFS([140R]:[140R], 1, Date:Date, AND(IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 2), IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 2)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!