dimanche 17 janvier 2021

Formula to calculate if the date in one cell is greater than another cell not in date format

In cell B2, I have the format set as General.

Cell C46 has this formula: =TODAY()

The formula in cell B2 is: =TEXT(C46,"m")&" - "&TEXT(INDEX(Data!N:V,MATCH(G60,Data!V:V,0),2),"d")

what this formula does (at least i think), is combine two other cells that are also in General format.
The result is "1 - 5", which i'm happy with, because it gives the appearance of being a date

In cell A2, I have another formula, which is what i need help with. I want the formula to look at the first number in cell B2 and read it as a Month. Then, i want it to look at the second number in B2 (the number after the hyphen), and read it as a Day.

Finally, i want the formula in A2, which is reading B2 as a date, to tell me whether B2 is greater than or less than today's date. If it is greater than, then mark as "Paid". If not, then "Not Paid"

=IF(DATE(YEAR(B2),MONTH(B2),DAY(B2)<TODAY()),"Paid","Not Paid")

can't figure out why this isn't working though. I even tried changing B2 into the below formula, but still doesn't work.

=TEXT(C46,"m")&" - "&TEXT(INDEX(Data!N:V,MATCH(G68,Data!V:V,0),2),"d")&" - "&YEAR(TODAY())

any help would be great.. please let me know if any of the above wasn't clear

Aucun commentaire:

Enregistrer un commentaire