Forum Discussion
Get earliest posted date within given month and year from range of dates
Hello all. I've been banging my head trying to find a way to get a particular date from a range of dates.
Here's the scenario; I have two named cells on a worksheet, one with the month August as a value named NisConMth and the other with 2022 as the value named NisConYr. Both were derived from formulas to retrieve the Year and Month from a date cell named PayDate posted by a macro on another Sheet:
- NisConYr formula is =YEAR(PayDate) 'Result is 2022
- NisConMth formula is =TEXT(PayDate-WEEKDAY(PayDate,3),"mmmm") 'Result is "August"
- I have to use TEXT because I need the text value of the month, in this case "August", to use as the Sheet name (January-December Sheets) in other formulas to retrieve data from the displayed month sheet. I use INDIRECT(NisConMth & "!ApplicableColumnRange") to get to the sheet and range I need to extract data.
- I use WEEKDAY(PayDate,3) to calculate if the Monday before the PayDate falls in the prior month, if it does, it gets posted to that prior month as evident in the last date below
As an example, the dates shown below are recorded on the August Sheet and I need to get 2022-08-05 as the value into a cell. That date is the first PayDate of August, 2022.
As the Title suggests I need a formula to Get the earliest day (the 5th) within given month (August) and year (2022) from range of dates below. Any help would be greatly appreciated. Thanks in advance.
2021-08-06
2021-08-13
2021-08-20
2021-08-27
2021-08-31
2021-09-03
2022-08-05
2022-08-12
2022-08-19
2022-08-26
2022-08-31
2022-09-02
Solution: Column B is where the dates are. Array MIN, IF and Year returns the earliest date for year 2022, from range B9:B1009
{=MIN(IF(YEAR(INDIRECT(NisConMth & "!B9:B1009"))=NisConYr, INDIRECT(NisConMth & "!B9:B1009")))}
I did not need to include the month in the criteria since it was already in the month sheet. Looking for the earliest date within the latest year was the solution
3 Replies
Solution: Column B is where the dates are. Array MIN, IF and Year returns the earliest date for year 2022, from range B9:B1009
{=MIN(IF(YEAR(INDIRECT(NisConMth & "!B9:B1009"))=NisConYr, INDIRECT(NisConMth & "!B9:B1009")))}
I did not need to include the month in the criteria since it was already in the month sheet. Looking for the earliest date within the latest year was the solutionIf I understand your description correctly, all dates on the August sheet are within the pay range for August. So can't you simply use the MIN function to get the earliest date?
- There's a previous Year, 2021, on that sheet
