We have date range column and want to count monthly totals

Sborror

We have project where we are migrating customers weekly - want to capture the total number of completed migrations by month. I have completed date and a check box when the customer is migrated. How do I do that range for the month?

Genevieve P.

Hi @Sborror

You'll want to put the formula in a Text/Number column because the output will be a Month Number. So if your date is 09/29/21, the formula will output "9", does that make sense?

Also, I see you have the number 2 after your column name. This says that you want to bring back data from row two, but instead we want it to pull back data from this row which is why we use @row

Try this in a Text Column:

=MONTH([Target Migration Complete Date]@row)

Cheers,

Genevieve

Genevieve P.

Hi @Sborror

Do you have a Date Column in this sheet, such as the System Generated "Created" Date column?

If so, you can use the MONTH function to automatically pull the Month number from that cell in a helper column, like so:

=MONTH(Created@row)

Once you have the associated Month number, then you can create a Report to Filter by your criteria (ex. if the box is checked), and Group the Report by this Month Number, using the Summary function to COUNT the number of rows per month.

Here's a webinar that goes through Grouping and Summary in Reports: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

Sborror

Sorry - here's what happened if I use the formula --- I put in

=MONTH([Target Migration Complete Date]2@row) -- if I remove the @row it's expecting a date -- and the properties of the column are date.

Genevieve P.

Hi @Sborror

You'll want to put the formula in a Text/Number column because the output will be a Month Number. So if your date is 09/29/21, the formula will output "9", does that make sense?

Also, I see you have the number 2 after your column name. This says that you want to bring back data from row two, but instead we want it to pull back data from this row which is why we use @row

Try this in a Text Column:

=MONTH([Target Migration Complete Date]@row)

Cheers,

Genevieve

Sborror

duh - sorry - thank you!!! this worked!

Genevieve P.

Haha no problem at all! Glad it worked for you 🙂