| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
