Excel/Calculate week ending from current date
QUESTION: Considering each row in my spreadsheet is a data item with current date. I want to be able to calculate week ending date and sum up or collate the data items for each week. How do I do that? thank you very much!
what do you consider the week ending date? Assume it is Friday.
Assume in row 2, the date in question is in C2. Further assume that dates in column C will fall on Monday - Friday (no weekend dates)
then you can get friday of that week with
If it is not Friday you want and or the dates may be on weekends, then post back with the specifics.
if you want to sum all values in column D for dates in column C between 3 Dec 2012 and 7 Dec 2012 then you can do
if you used the formula to produce the Friday date in say column E, then you could do
Again, I have had to make assumptions about what data you have, what your rules are and so forth. If you can not adapt what I suggest to you specific situation, then post back with your details. Also, the SUMIFS formula (with an S on the end) is only available in Excel 2007 and later. If you have an earlier version you could just do
---------- FOLLOW-UP ----------
QUESTION: Thank you so much Tom! I should have given you more detail. I am working with pivot tables. The source data consists of "Activities", one activity per row with the date the activity began as a data field in the row. I now need to generate reports of these activities over a period of 7 days (week). Ideally, I would like to be able to generate a field in the source data via VLOOKUP that would identify each row with that week ending date or some identifier that would enable me to pivot on this new field so that I can aggregate the information over each 7 day period. I hope this makes more sense. If it doesn't, I can add a sample table to the question. Thanks very much for the help!!
Based on what you say, I answered the question
would put the ending period if it ended on friday.
now if you want a 7 day week, then I assume it still starts on Monday, so
gives the Sunday at the end of the Week. You would then use that column in your pivot table to limit you output to that ending date.
If you need more help than that, then send a sample data sheet to firstname.lastname@example.org with a complete explantion of what you want to do.