Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hello again... complicated macro using dates
hello me again.
i've had a look at previous posts but am still a bit stuck! i have a workbook. there are two worksheets. one is for 2004 and one is for 2005, soon I will have 2005 and 2006, but we can talk about that later. there are various columns. i am particularly looking at two... date column and amount column. the date column does not run sequentially, the dates are ad hoc, eg, they do not run 21/22/23 it may be 20/22/23/27. i have a macro which will add the values in the amount columns from both sheets. but what i would like it to do is... look at today's date, and find the corresponding date in the 2004 sheet and add everything from that date on, so it is effectively a 12 month rolling period. any suggestions would be greatly appreciated. thanks in advance mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hello again... complicated macro using dates
The SUMIF worksheet function should be able to do this - for the prior year's
sheet it would be (substituting in the proper addresses for the date range and amount range): =SUMIF(DateRange,"="&TEXT(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),"mm/dd/yy"),AmountRange) I assume you can figure the total from the current year, since presumably all amounts are before today's date, but if not the form of the formula would be the same. -- - K Dales "mike" wrote: hello me again. i've had a look at previous posts but am still a bit stuck! i have a workbook. there are two worksheets. one is for 2004 and one is for 2005, soon I will have 2005 and 2006, but we can talk about that later. there are various columns. i am particularly looking at two... date column and amount column. the date column does not run sequentially, the dates are ad hoc, eg, they do not run 21/22/23 it may be 20/22/23/27. i have a macro which will add the values in the amount columns from both sheets. but what i would like it to do is... look at today's date, and find the corresponding date in the 2004 sheet and add everything from that date on, so it is effectively a 12 month rolling period. any suggestions would be greatly appreciated. thanks in advance mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difference between dates, but a bit more complicated | Excel Discussion (Misc queries) | |||
Need Help with Complicated Array Macro | Excel Discussion (Misc queries) | |||
HELP with complicated macro | Excel Discussion (Misc queries) | |||
How do I do this complicated macro??? | Excel Worksheet Functions | |||
One more and I'll finish this complicated macro | Excel Programming |