CODEWITHSUNDEEP
excelfunctionformula
10101
10101

Reputation: 2402

Excel TODAY() function in IF formula doesn't work properly

I have "Microsoft MonthView Control 6.0 (SP4)" component that enters the date to cell "D17" I want my function to compare today date to cells "D17" date and give certain message. So if date in cell "D17" is older than current date it will display "OFFER EXPIRED" message. In cell "D17" I currently have 1.10.2018. I use formula =IF(D17>TODAY();"OFFER EXPIRED";"") but it doesn't work. I have tried different cell Formatting options with no success. Any ideas where the problem can hide?

Upvotes: 2

Views: 2339

Answers (2)

Bishu
Bishu

Reputation: 26

Assuming that the date you have is in string format & in dd.mm.yyyy format. Use below code for the comparison:

=IF(DATE(
   RIGHT(D17,4),
   MID(D17,FIND(".",D17,1)+1, (
      FIND(".",D17,FIND(".",D17,1)+1)- 
      FIND(".",D17,1))-1),
   LEFT(D17,FIND(".",D17,1))
   )<TODAY(),
"OFFER EXPIRED","")

Upvotes: 0

pnuts
pnuts

Reputation: 59460

Please try:

 =IF(1*SUBSTITUTE(D17;".";"/")>TODAY();"OFFER EXPIRED";"")

(Seeks to coerce Text 1.10.2018 into Number before making the comparison.)

Upvotes: 2

Related Questions