Need help on excel

[TariqRaza]

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 month and rest should be the end of the month

(Note if cell F6 is with AR Only 1st date should come with 1st date of the month rest should be the end of the month), if not AR all should come with the end of the month date.

i am really thankful

Regads

Tariq

[#2]

Hi @TariqRaza 

You can use the Row() function to restrict the desired result only for the first date. The below formula may help you to achieve your requirements.

=IF(ROW(B9)>9,EOMONTH(B9,0),IF($F$6="AR",(DATE(YEAR(B9),MONTH(B9),1)),EOMONTH(B9,0)))

A sample working file is also attached for more understanding.

Please let me know if it works for you

Note: In case your data starts from another line, you will have to slightly modify the formula.

Thanks

Tauqeer

[#3]

@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.