Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Year
I am currently using the formula =SUMIF(Master!$A6:$A1466,"=" &
DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),Master!B6:B1466) to calculate figures for the previous rolling year. However the issue i am having is that it seems to be rounding to each month rather than daily as i require, i presume this is due to the fact that there is Month in the formula, i have tried using Day instead but have had no joy. Can anyone help? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Year
the last 1 in the DATE functtion is comparing againstt the 1stt of the month.
to make it daily try =SUMIF(Master!$A6:$A1466,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),Master!B6:B1466) You may need to adjust by one day. If today is March 5th do you want March 5 2006 to March 4 2007 or March 6 2006 to March 5 2007, or March 5 2006 to March 5 2007 "Tim Halpin (BV)" wrote: I am currently using the formula =SUMIF(Master!$A6:$A1466,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),Master!B6:B1466) to calculate figures for the previous rolling year. However the issue i am having is that it seems to be rounding to each month rather than daily as i require, i presume this is due to the fact that there is Month in the formula, i have tried using Day instead but have had no joy. Can anyone help? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rolling Year
=SUMIF(Master!$A6:$A1466,"="
&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),Master!B6:B1466) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tim Halpin (BV)" wrote in message ... I am currently using the formula =SUMIF(Master!$A6:$A1466,"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),Master!B6:B1466) to calculate figures for the previous rolling year. However the issue i am having is that it seems to be rounding to each month rather than daily as i require, i presume this is due to the fact that there is Month in the formula, i have tried using Day instead but have had no joy. Can anyone help? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rolling year | Excel Discussion (Misc queries) | |||
how to make a rolling calender where hours roll off after a year | Excel Discussion (Misc queries) | |||
Rolling Year in Excel | Excel Discussion (Misc queries) | |||
rolling year in excel | Excel Worksheet Functions | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions |