Calculate number of days left including today

[Ironman]

Hi

A2 contains today's date

=UPPER(TEXT(TODAY(),"d mmm yyyy"))

A6 contains the formula

=DATE(YEAR(A2),12,31)-A2

which is the number of days remaining in the current year.

Column A12 down contains dates from Jan 1 1998 onwards.

What I'm after is an amended formula for A6 so if the last cell in Col A does not also contain today's date then the formula in A6 adds 1 to the result (and if the last cell does contain today's date then the A6 formula result remains as it is).

If it can only be done with VBA then I'd also be grateful for a bit of worksheet_change event code that will enable this.

Many thanks!

 

[Ironman]

Resolved

=DATE(YEAR(TODAY()),12,31)-LOOKUP(10^35,A:A)

 

[Sahak]

Maybe this?
=IF(INT(A2)=INT(LOOKUP(1,1/(A:A<>""),A:A)),DATE(YEAR(A2),12,31)-A2,DATE(YEAR(A2),12,31)-A2+1)

 

[Ironman]

Many thanks for your solution Sahak!

 

[Sahak]

You are very welcome.