Current bi-weekly total

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul_of_Abingdon - 14 Mar 2008 15:48 GMT

In my worksheet, Column A1 has project start date and A2 to A3000 has
subsequent dates based on A1. Column B has data. After each 14 days, there is
bi-weekly total. I would like to add a row on top, which will show me
bi-weekly total for the current week. So, let's say, if project start in
January 6, 2008, and if today is March 14, I want be able to see the most
current bi-weekly total. Is there a way to do that. I was able to do "Year to
Date" total by just simply adding those biweekly total.
Since, it is a long sheet, I have scroll down to find out current bi-weekly
total. I want avoid that. So, the most current bi-weekly total always will be
in top row.

Thanks in adavance.

Reply to this Message

Ron Coderre - 14 Mar 2008 15:56 GMT

Try this:

=SUM(OFFSET(B1,MATCH(TODAY()-WEEKDAY(TODAY()),A1:A3000,0),,14))

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> In my worksheet, Column A1 has project start date and A2 to A3000 has
> subsequent dates based on A1. Column B has data. After each 14 days, there
[quoted text clipped - 12 lines]
>
> Thanks in adavance.

Reply to this Message

Paul_of_Abingdon - 24 Mar 2008 16:16 GMT

I think it will work. But I made a mistake providing information. This is
what I forgot to tell:

In my sheet, after every two weeks there are two rows: one is subtotal,
which is total of two weeks, and other is year to date, which is total of all
subtotals above. So A2 to
A15 have data, A16 has subtotal of A2 to A15. And A17 has total of that
subtotal.

Thus, in A32, there will be another subtotal of A18 to A31. A33 will have
total of subtotals from A31 and A16. And so on.

> Try this:
>
[quoted text clipped - 26 lines]
> >
> > Thanks in adavance.

Reply to this Message