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

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
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:

Some videos you may like

Excel Facts

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

Ironman

Board Regular
Joined
Jan 31, 2004
Messages
186
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
Joined
Jan 31, 2004
Messages
186
Ah, I've just managed to do it...

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

Thanks again!
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,527
Messages
5,523,391
Members
409,515
Latest member
chemitek

This Week's Hot Topics

Top