Forum Discussion

TariqRaza's avatar
TariqRaza
Copper Contributor
Nov 17, 2021

Need help on excel

Hi All,   Need help if any can.   I have a formula, I need to adjust the formula with criteria if in cell F6 "AR" so result  of1st date of an array must come with 1st day of the left side dated m...
Excel 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 12, 2021

TariqRaza 

This picks out the first date by the fact that it is the smallest.  A dynamic array formula could be

= IF((date=MIN(date))*(AR="AR"),
      1+EOMONTH(+date,-1),
        EOMONTH(+date,0))

where EOMONTH has been used for both the first and last days of a month.  As an alternative, one could calculate whether the end of month formula needs adjustment and then does so in a single expression

= LET(
    adjust, (date=MIN(date))*(AR="AR"),
    paymentPeriod, adjust + EOMONTH(+date,-adjust),
    paymentPeriod)

Note: computationally the introduction of the 'paymentPeriod' variable achieves nothing; it is used simply to explain the intent of the calculation.