Automating start date COUNTIF formula for Visual Data

Hello,

I am trying to visualize new hire data in a chart for our execs to see how many users we're onboarding per month.

It took me awhile to find a working formula, but I settled on this -

=COUNTIFS([Start Date]:[Start Date], >=DATE(2022, 1, 1), [Start Date]:[Start Date], <=DATE(2022, 1, 31))

Now this formula just grabs the amount of new hires for the month of January.

So I would have a column saying "January" then this formula next to it referencing the other smartsheet to grab the data.

Is there a way to automate this so I do not have to manually keep adding months and copy/pasting the formula, tweaking the numbers every time.

Thank you!

Find more posts tagged with

Accepted answers

All comments

Hollie Green

Thank you for asking this question it helped me think outside the box for an issue I was having.

I would need more details to help specifically but the below information may help you figure it out on your own. For example, I don't know if you need just for one year or if it crosses multiple years and if you can add columns to the other sheet you are referencing, etc.

=Month[Start Date] would bring in just the month of the start date as a number 1-12 so January would be 1 and so on.

=Month(Today()) will bring in the number of this month so since it is July it will bring in 7.

I incorporated these into formulas to assist with my issue. Hope they help resolve yours.

Bluejay

Hi Hollie thanks for your response.

Your formulas will be of help thank you, but I'm also asking if there is a way to automate on the report i'm creating

So if for instance, August is not typed in yet by me, and we get a new hire that starts 8/12/22, I would want a new row to be generated to say

"August" "1" <-- to indicate that there is currently 1 new hire for the month and to dynamically update.

Then when new hires start hitting september, I want a row to be created automatically "September" and so on and so forth. Not sure if this is possible

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.