[Solved] Return all dates in a specific month

[patlee468]

I have a document in which the form is listed in the columns, the names of the accounts are the rows, and the date it is due is in the cell. I want to return all forms due in January for instance. Can I do this with Excel?

 

[Yousuf Shovon]

I have a document in which the form is listed in the columns, the names of the accounts are the rows, and the date it is due is in the cell. I want to return all forms due in January for instance. Can I do this with Excel?

Hello Patlee,
Welcome to ExcelDemy Forum! Thanks for sharing your experience. I understand you want to return all the given forms in a specific month.
If your Due Dates are truly entered as valid dates as given in the below image, you should be able to apply a simple filter, i.e. Date on or after January 1 and before or on January 3.

If you want to return the list of all forms/data due in a specific month, for instance, January to a new location, you can use the new FILTER function. Here are the steps:
1. Enter the below formula to a blank cell.

=FILTER(A2:B6,(B2:B6>=DATE(2024,1,1))*(B2:B6<=DATE(2024,1,31)))

2. Press Enter.
As a result, you get the extracted rows from the range A2:B6 where the corresponding values in column B fall within the date range from January 1, 2024, to January 31, 2024.

Note: The FILTER function is only available in Excel 365, Excel 2019, and later versions that support dynamic array functions.
If you are using an older Excel version, you can use an array formula to return the list of forms due in specific months. Here are the steps:
1. Insert the below formula where you want to see the list.

=IFERROR(INDEX($A$2:$B$6, SMALL(IF(($B$2:$B$6>=DATE(2024,1,1))*($B$2:$B$6<=DATE(2024,1,31)), ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1), ROW(1:1)), COLUMN(A1)), "")

2. Press Ctrl + Shift + Enter keys.
3. Drag the formula down until it shows the full list.

As a result, you obtain values from columns A and B (specified range: $A$2:$B$6) based on a date range condition in column B. The formula uses array functions like IF, INDEX, and SMALL to dynamically extract the data meeting the specified date criteria, and IFERROR is used to handle any errors by returning an empty string.
I am attaching the Excel file here. Thank you.

Regards,
Yousuf Shovon

 

[patlee468]

Thank you so much!!!