Help with DATEDIF function dependent on if today's date appears in last row of column

[Ironman]

Hi

I have the following formula in Cell A10:

=(DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"d"))+1

This calculates the number of potential activity days left in the year.

However, as it's only based on the calendar, the result is incorrect when the current day's activity has taken place.

What I'd be grateful for this formula to be amended, so if the current date appears in the last filled cell in Column A, it subtracts 1 from the result.

Many thanks!

 

[Phuoc]

Try

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

 

[Ironman]

Brilliant! Many thanks indeed Phuoc, that works perfectly!

Could I also please ask you how I can use your formula if I want to calculate the number of days gone in the current year (again, taking account of if today's date appears in the last filled cell in Column A)?

Thanks again!

 

[Ironman]

Ah, I've just managed to do it...

=-(DATE(YEAR(TODAY()),1,1)-LOOKUP(10^35,A:A))+1

Thanks again!