a Month to Date and Year TD page in one sheet changing each day?

G

Guest

Can a single Excel worksheet be created to calculate month-to-date and
year-to-date with input on a daily basis, changing the daily input, but
keeping the previous values for MTD and YTD?
 
R

Roger Govier

Hi Mark

Not sure if I fully understand your request, but assuming you wanted to
see the totals up to yesterday, whilst inputting today's values then
this can be achieved fairly easily. Obviously, as soon as you open the
sheet tomorrow, values entered today would be included, but then not the
values being input during the day again

With Dates in Column A, starting at row 2, and values in column B,
for Month to date enter in cell C1
=SUMPRODUCT(--(MONTH($A$2:$A$1000)=MONTH(TODAY())),
--($A$2:$A$1000<=--TODAY())-1),$B$2:$B$1000)

For Year to Date enter in cell D1
SUMPRODUCT(--($A$2:$A$1000>=DATE(YEAR(TODAY()),1,1)),
--($A$2:$A$1000<=--TODAY()-1),$B$2:$B$1000)

Change the ranges to suit but ensure that they are of equal size.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

converting workbook from one month to the next 1
how to change date accepting format to day-month-year 2
Adjusting formula for month in vlookup? 3
Look up in another sheet for values to return in 1st worksheet 2
converting year to date data to month to date 3
Enter day, default month and year 2
Date(Year,Month,Day) not returnign correct date 6
How do I calculate a value based on dates? 5

Top