Wanting to count dates within a column

First, this

=COUNT(Actual Live Date1:Actual Live Date999)

does not get the whole column. It will generate an #UNPARSEABLE error because the column names have spaces in them and require square brackets []. If that were fixed, it would only get the first 999 rows.

This will get the whole column

=COUNT([Actual Live Date]:[Actual Live Date])

In the blue cell, this formula will get the Actual Live Dates that are October.

=COUNTIF([Actual Live Date]:[Actual Live Date], IFERROR(MONTH(@cell), 0) = 10)

That may not quite be what you want, however, so this will get October 2017

=COUNTIFS([Actual Live Date]:[Actual Live Date], IFERROR(MONTH(@cell), 0) = 10, [Actual Live Date]:[Actual Live Date], IFERROR(YEAR(@cell), 0) = 2017)

If that work for you, I recommend you have somewhere that you enter 10 and 2017 so you don't need to modify the formula.

Even better would be a cells for "Month of Concern" and "Year of Concern". If that was just a date cell that isn't being used by other formulas (or accounted for by them), then that would be even easier. Let's say you put it into row 7 of the Actual Live Date (the same row as the blue cell)

=COUNTIFS([Actual Live Date]:[Actual Live Date], IFERROR(MONTH(@cell), 0) = MONTH([Actual Live Date]7), [Actual Live Date]:[Actual Live Date], IFERROR(YEAR(@cell), 0) = YEAR([Actual Live Date]7)) - 1

Note that I did not wrap the new part (in bold) with IFERROR because I expect there to be a date there. The -1 at the end accounts for this new entry. 

The last caveat would be it is better if that month/year of concern was somewhere else ... because someone else may not realize they need to account for it in their formulas.

I hope this helps.

Craig

There are so many ways to go on what you are asking for, that it is impossible to give a definitive answer.

I can build rolling years, rolling quarters, current month, previous week, whatever is needed by the user. But it all depends on what, exactly, is needed.

If you are looking for YTD, then something with YEAR(date) = YEAR(TODAY()) should be sufficient.

If you are looking for current month, then something with AND(MONTH(date)=MONTH(TODAY()),YEAR(date)=YEAR(TODAY())) should be in the tool box.

Last month? More complicated because late month may be December.

I just finished creating a rolling 5 quarter system for a customer, with dashboard that will automatically change from "Current Quarter April - June 2018" to "Current Quarter July - September 2018" on July 1st. All the other quarters (previous, next three) will update too.

But that took me a couple days and isn't a single formula that would be easy to share.

One bit of advice, though, is that it is better to use "Current Year" than "2018" because any Reports or Dashboards don't need to be updated for the current values, but would it they were pointing to a sheet that had 2018 in the name.

I hope this helps. 

Craig

@cell is a reference to the cell returned by the formula

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Can you post your formula or screen shot same?

Craig

Hi,

Try this.

Formulas

Weekstart: 

=IFERROR(WEEKNUMBER(Startdate@row); "Week from Startdate")

Number of projects per week: 

=COUNTIF(Weekstart:Weekstart; Weeknumber@row)

Dashboard Text:

=(" Week " + Weeknumber@row + " has " + [Number of projects per week]@row + " Project Starts ")

Please see the attached link/screenshot for more information.

Number of projects per week

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

You could also have a reference sheet for the weeks.

Would this work for you?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting