MTD / YTD

[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!

 

[Jacob Skaria]

With date in A1

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

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

If this post helps click Yes

 

[Luke M]

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.

 

[Glenn]

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.

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

 

[Shane Devenshire]

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.

 

[Shane Devenshire]

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)

 

[Jacob Skaria]

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