CODEWITHSUNDEEP
excelexcel-formulaexcel-365
Raja Tanwar
Raja Tanwar

Reputation: 1

Excel command to find whether a particular month lies between two dates or not

Below are the few dates and I want to know how to check whether Dec-2024 falls between these two dates or not.

Similarly find whether Jan-2025 lies between these dates or not

enter image description here

I have tried If & And + Value + Text and many nested ifs, datedif also not useful.

Upvotes: 0

Views: 113

Answers (4)

rotabor
rotabor

Reputation: 4528

Here the extended variant which detects any part of a month falls into the time period (for December 2024):

=LET(mon,DATE(2024,12,1),IF(AND(DATE(YEAR(mon),MONTH(mon)+1,1)>A2,mon<=B2),"Yes","No"))

This formula can be used as the name definition (Formulas --> Name Manager --> New...)

IsMonthInPeriod=LAMBDA(dyear,dmon,pstart,pend,
  IF(AND(DATE(dyear,dmon+1,1)>pstart,DATE(dyear,dmon,1)<=pend),"Yes","No"))

Then

[C2]=IsMonthInPeriod(2024,12,A2,B2)
[E2]=IsMonthInPeriod(2025,1,A2,B2)

Upvotes: 1

derpirscher
derpirscher

Reputation: 17382

So if I understand you correctly, the result should be "YES" when at least one day of the period in question falls into the respective month? Then the following formula should do the trick

=IF(AND(
   DATE(YEAR(C1), MONTH(C1), 1) <= DATE(YEAR(A1),MONTH(A1),1), 
   DATE(YEAR(D1), MONTH(D1), 1) >= DATE(YEAR(A1),MONTH(A1),1)
   ), "YES", "NO")

Expecting start of the period is in cell C1, end of the period is in cell D1 and a date from the respective month is in A1

Upvotes: 2

P.b
P.b

Reputation: 11468

This is also an option if you have Office 365 with ETA:

=LET(e,EOMONTH,
     i,e(45627,{0,1}),
IF((i<e(+A2:A4,0))+(i>e(+B2:B4,0)),"No","Yes"))

PS instead of saying between or not I'd refer to it as month included

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36870

You may try the following formula-

 =IF(AND(DATE(2024,12,1)>=A2,DATE(2024,12,1)<=B2),"Yes","No")

For JAN-2025 try-

=IF(AND(DATE(2025,1,1)>=A2,DATE(2025,1,1)<=B2),"Yes","No")
Effective From Effective To Dec Fall in Between or NOT
27-Oct-24 29-Mar-25 Yes
27-Oct-24 22-Nov-24 No
27-Oct-24 01-Dec-24 Yes

Upvotes: 1

Related Questions