formula

Sue van Niekerk

=COUNTIF({Resolved Date Range}, ="Resolved", IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Hi Guys, need some advice- need to create a formula (above) that will count the number of tickets "Resolved" from the top sheet in the ticket status column

and will count the number "Request Date" per week, month, year - to report into bottom table

MCorbin

First - I recommend that you create some helper fields for your dates. It makes your formulas a lot easier. In my screenshot, these are the left 2 columns. You can hide them after you set up your formulas.

Formulas for:

Sunday this week: =TODAY(1 - WEEKDAY(TODAY()))

Saturday this week: =TODAY(7 - WEEKDAY(TODAY()))

Sunday Last week: =TODAY(1 - WEEKDAY(TODAY())) - 7

Saturday Last Week: =TODAY(7 - WEEKDAY(TODAY())) - 7

Test Date: =DATE(YEAR(TODAY()), MONTH(TODAY()), 15)

Last Month: =[Test Date]6 - 30

About Test Date and Last Month: When you are using formulas looking for data this month, last month, 2 months ago, etc., I find that it's best to use a date in the middle of the month to calculate what last month would be and 2 months ago, etc. The reason for this:

If I used =Month(today())-1, This formula would work for every month except January - you'll get an error in January when you're looking for the previous month by calculating this month -1 (there is no month 0). By taking a date in the middle of the current month and subtracting 30 days, you'll always get a date in the middle of the previous month, against which you can compare your other dates.

Now, for your metrics - you're going to refer to these "test date" fields for several of your metrics:

Year to Date Resolved: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR(TODAY()))

Issues Resolved Last Month: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR([Test Date]7), [Resolved Date]:[Resolved Date], MONTH(@cell) = MONTH([Test Date]7))

Issues Resolved This Month: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], YEAR(@cell) = YEAR(TODAY()), [Resolved Date]:[Resolved Date], MONTH(@cell) = MONTH(TODAY()))

Issues Resolved Last Week: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], >=[Test Date]3, [Resolved Date]:[Resolved Date], <=[Test Date]4)

Issues Resolved This Week: =COUNTIFS([Resolved Status]:[Resolved Status], "Resolved", [Resolved Date]:[Resolved Date], >=[Test Date]1, [Resolved Date]:[Resolved Date], <=[Test Date]2)