COUNTIF/SUM formula question for 'to-dos' this month and this year

[rkobrya]

I have a basic 'to-do' list and I want to see how many of my tasks are due today, tomorrow, this week, this month, and this year.

[pre]

Project	Description	Due Date	Status
Learn Excel	Learn Excel Tables	10/18/12	Pending
Learn Excel	Learn Excel Sparklines	10/19/12	Pending
Learn Excel	Learn Excel Conditional Formatting	10/19/12	Pending

[/pre]

I have formulas for today= =COUNTIF(Table2[Due Date],TODAY())

tomorrow =COUNTIF(Table2[Due Date],TODAY()+1)

this week =SUM(IF(Table2[Due Date]>=(TODAY()-WEEKDAY(TODAY(),2)+1),IF(Table2[Due Date]<(TODAY()-WEEKDAY(TODAY(),2)+1+7),1,0),0))


I need help with formulas for this month and this year.


thanks

 

[Haseeb A]

First of all, i would recommend you to enter TODAY() in a cell & reference the cell in the formula. This way can reduce recalculation time of volatile TODAY() formula.


I entered in A1 = TODAY()


Week:


=COUNTIF(Table2[Due Date],">="&A1-WEEKDAY(A1,2)+1)-COUNTIF(Table2[Due Date],">"&A1-WEEKDAY(A1,2)+7)


Month:


=COUNTIF(Table2[Due Date],">="&EOMONTH(A1,-1)+1)-COUNTIF(Table2[Due Date],">"&EOMONTH(A1,0))


Year:


=COUNTIF(Table2[Due Date],">="&DATE(YEAR(A1),1,1))-COUNTIF(Table2[Due Date],">"&DATE(YEAR(A1),12,31))

 

[rkobrya]

Haseeb A, Thank you so much! Works perfect!


I'm just getting started with Excel and working on my to-do/goals spreadsheet which will have some about learning more about excel.


You're awesome! Thanks again.