samedi 28 décembre 2019

How to generate a payroll condition using array formula

I want to make a condition that if the day is more than or equal 16 and less than or equal 31 the formula returns the same month adding to it the word payroll and if the day is from 1 to 14 formula returns previous month adding to it the word payroll

The equation is working but without the array formula and I need it in array to auto drag

Here is the equation without the array formula:

=IF(A2="","",if(AND(B2>=16,B2<=31),TEXT(DATE(2019,C2,1),"MMM"),TEXT(DATE(2019,C2-1,1),"MMM"))&" Payroll")`

Here is the equation in the array formula:

=ARRAYFORMULA(IF(ROW(A:A)=1,"Payroll Array",IF(A:A="","",if(AND(B:B>=16,B:B<=31),TEXT(DATE(2019,C:C,1),"MMM"),TEXT(DATE(2019,C:C-1,1),"MMM"))&" Payroll")))

here is a sample datasheet to see the difference as the array formula doesn't return the correct value I need: Link

Aucun commentaire:

Enregistrer un commentaire