MTD / YTD
 

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!

MTD / YTD
 

With date in A1

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Greg" wrote:

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!

MTD / YTD
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greg" wrote:

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!

MTD / YTD
 

Luke M wrote:
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.

MTD / YTD
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Greg" wrote:

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!

MTD / YTD
 

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)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Glenn" wrote:

Luke M wrote:
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.

MTD / YTD
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With date in A1

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Greg" wrote:

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!