CODEWITHSUNDEEP
excelexcel-formula
Igor Ilgiyaev
Igor Ilgiyaev

Reputation: 3

How to show text when date is ended in excel

i have a question i'm trying to show a text when Some DATE is over. i used this code to show the remaining date:

    =DATEDIF($J$1,B6,"Y") & " years, "&DATEDIF($J$1,B6,"YM") & " months, "&DATEDIF($J$1,B6,"MD")& " days "

and it works fine now i need to show the text when ended i tried this code but its not working

    =if(DATEDIF($J$1,B6,"Y") & " years, "&DATEDIF($J$1,B6,"YM") & " months, "&DATEDIF($J$1,B6,"MD")& " days ","Date Ended",DATEDIF($J$1,B6,"Y") & " years, "&DATEDIF($J$1,B6,"YM") & " months, "&DATEDIF($J$1,B6,"MD")& " days ")

Does any one have another way???

thanks in advance

Upvotes: 0

Views: 48

Answers (2)

teylyn
teylyn

Reputation: 35915

There's probably some language problem in the mix, but maybe

=IF($J$1-B6>0,"Date ended",DATEDIF($J$1,B6,"Y") & " years, "&DATEDIF($J$1,B6,"YM") & " months, "&DATEDIF($J$1,B6,"MD")& " days ")

... which means, if the date difference is 0 then show the text "Date ended", else show the calculated difference.

Upvotes: 0

Shauno_88
Shauno_88

Reputation: 665

The below should work for you. I've added in an IF statement to check if the date is greater larger. If it is then it shows "TRUE" and displays the date, if not then it shows your previous value:

=IF($J$1-B6>0,"True ("&TEXT(B6,"dd mmmm yyyy")&")",DATEDIF($J$1,B6,"Y") & " years, "&DATEDIF($J$1,B6,"YM") & " months, "&DATEDIF($J$1,B6,"MD")& " days ")

Upvotes: 1

Related Questions