Franz Verga wrote:
Dipwind wrote:
Hi,

I have a problem, and alone I simply can't get a solution, because I
don't Know how.




First of all I'll suggest you to use the function TODAY(), instead of
NOW(), because NOW has also the time inside an not only the date.

To make the cells red, you can use the conditonal formatting (First
of all select the cells you want to format, then menu Format,
Conditional Formatting, choose "formula is" and type: =$D4<"", then
click on Format and choose the colour red for background).

To stop counting years, you can use an IF function in G4; to have the
counting in years, months and days, you can use the DATEDIF function,
so the formula in G4 should be:

=IF(D4<"",DATEDIF(C4,$B$1,"Y")&" Y "&DATEDIF(C4,$B$1,"YM")&" M
"&DATEDIF(C4,$B$1,"MD")&" D",DATEDIF(C4,D4,"Y")&" Y
"&DATEDIF(C4,D4,"YM")&" M "&DATEDIF(C4,D4,"MD")&" D")


sorry, the above formula should be:

=IF(D4<"",DATEDIF(C4,D4,"Y")&" Y "&DATEDIF(C4,D4,"YM")&"
M "&DATEDIF(C4,D4,"MD")&" D",DATEDIF(C4,$B$1,"Y")&" Y
"&DATEDIF(C4,$B$1,"YM")&" M
"&DATEDIF(C4,$B$1,"MD")&" D")

but a better solution could be:

=IF(D4<"",DATEDIF(C4,D4,"y")&" y
"&IF(DATEDIF(C4,D4,"YM")=0,"",DATEDIF(C4,$B$1,"YM" )&"
m")&IF(DATEDIF(C4,D4,"md")=0,"",DATEDIF(C4,D4,"MD" )&"
D"),DATEDIF(C4,$B$1,"y")&" y
"&IF(DATEDIF(C4,$B$1,"YM")=0,"",DATEDIF(C4,$B$1,"Y M")&" m
")&IF(DATEDIF(C4,$B$1,"MD")=0,"",DATEDIF(C4,$B$1," MD")&" D"))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy