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

Ironman

Board Regular
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!

Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Phuoc

Board Regular
Try

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

Ironman

Board Regular
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

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

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

Thanks again!

Last edited:

Replies
1
Views
54
Replies
0
Views
41
Replies
5
Views
76
Replies
1
Views
87
Replies
4
Views
111