MTD / YTD

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi all,
Probably a simple question but I'm stuck. What date formulas can I use to
summarize month to date and year to date data? I want to link it to todays
date. Please advice.
Thanks!
 
With date in A1

=DATEDIF(A1,TODAY(),"m")

=DATEDIF(A1,TODAY(),"y")

If this post helps click Yes
 
Lets say you have a list of dates in column A, values in column B
YTD:
=SUMIF(A:A,"<="&TODAY(),B:B)
MTD:
=SUMPRODUCT(--(MONTH(A2:A100)=MONTH(TODAY())),--(A2:A100<=TODAY()),B2:B100)

note that SUMPRODUCT can't reference entire columns (A:A:) unless using XL
2007.
 
Luke said:
Lets say you have a list of dates in column A, values in column B
YTD:
=SUMIF(A:A,"<="&TODAY(),B:B)
MTD:
=SUMPRODUCT(--(MONTH(A2:A100)=MONTH(TODAY())),--(A2:A100<=TODAY()),B2:B100)

note that SUMPRODUCT can't reference entire columns (A:A:) unless using XL
2007.
Click to expand...


Your formulas assume that there is only data for this year in the list.
 
Hi,

Turn on AutoFilter and open the one over your date field and choose Date
Filters, Year to Date, or This Month. That's if you are using 2007.
 
The general formula approach would be

YTD:

=SUMPRODUCT(--(YEAR(A1:A14)=YEAR(NOW())),B1:B14)

MTD:

=SUMPRODUCT(--(YEAR(A1:A14)=YEAR(NOW())),--(MONTH(A1:A14)=MONTH(NOW())),B1:B14)
 
Oops I misread... Another way

=SUMPRODUCT(--(TEXT(A1:A14,"mmyyyy")=TEXT(TODAY(),"mmyyyy")),B1:B14)

You can adjust mmyyyy to mm for referring just the month
and to yyyy for referring just the year....

If this post helps click Yes
 
Back
Top