How do I get my datasheet to show the months requests were opened and closed

Isla_Spellman

when my request tracker sheet's input is in calendar format? Current set up is as below, and I'd only like to use data from column A & B to ensure better accuracy in the data sheet:

KPH

I would do the opposite - change the format of the date column in the second sheet from text to date and populate this with the first of the month that you are interested in:

Month

Jan 2025
Feb 2025
Mar 2025

To

Month started

1/1/25
2/1/25
3/1/25

You can then perform COUNTIFS and similar functions using the Month started column.

For example, the formula below would count all the rows were the date in the first sheet was on or after the date in the month started column on row 1 and before the date in the month started column in row 2.

=COUNTIFS({Date column in other sheet}, >=[Month Starting]1 , [Date Completed]:[Date Completed], <[Month Starting]2)

You can make the formula more robust/complex by calculating the end of the month and using @row, rather than specifying the row number.

KPH

I would do the opposite - change the format of the date column in the second sheet from text to date and populate this with the first of the month that you are interested in:

Month

Jan 2025
Feb 2025
Mar 2025

To

Month started

1/1/25
2/1/25
3/1/25

You can then perform COUNTIFS and similar functions using the Month started column.

For example, the formula below would count all the rows were the date in the first sheet was on or after the date in the month started column on row 1 and before the date in the month started column in row 2.

=COUNTIFS({Date column in other sheet}, >=[Month Starting]1 , [Date Completed]:[Date Completed], <[Month Starting]2)

You can make the formula more robust/complex by calculating the end of the month and using @row, rather than specifying the row number.

Isla_Spellman

Thanks! I'll give that a try.

Isla_Spellman

Unfortunately I'm now just getting an #UNPARSABLE error and may contact support because I'm sure I'm the issue!

Paul Newcome

I would suggest a date type column in the target sheet (the one with the COUNTIFS formula) that has a day from each month in each row similar to the first part of @KPH's suggestion where you have the [Month Started] column, but then you can use the below as a column formula

=COUNTIFS({Other Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH([Month Started]@row), IFERROR(YEAR(@cell), 0) = YEAR([Month Started]@row)))

If this doesn't work, feel free to provide updated screenshots to include on of the formula open in the sheet as if you are about to edit it.