Formula to total for entire week

[Wskip49]

How do you change this formula to days Sunday thru Saturday a full week not just week days.

Please Login or Register  to view this content.

[MarvinP]

Hi,
Look for Weekday return_type
http://www.excelfunctions.net/Weekday-Function.html

[Wskip49]

I read the weekday function MarvinP suggested. I don't know what to change in the formula.

[jason.b75]

Moderation request removed.

Thanks for correcting your thread title!

[Wskip49]

I changed the title in my original post. I hope I have fixed the rule I did not meet.
Thanks

[jason.b75]

Trying to make sense of your formula, it looks like you're trying to summarise monthly data with an offset to count the whole week where the month begins of ends midweek.

Given that you are unsure of how to edit the formula, I would assume that it was written for you, in which case it would be best to ask the original author of the formula how to edit it as they will be the person most familiar with the method used.

The method I would use would be different to the one you have, but as Mac and Windows use different date systems, I'm not sure that my method would work correctly for you.

[Wskip49]

Thanks Jason.b75

[FDibbins]

This part is what is pulling out based on Sat/Sun...
WEEKDAY(DATE(2016,1,1))=7

Remove that (in both places) and see of that helps

[Wskip49]

Okay, I took WEEKDAY(DATE(2016,1,1))=7 out of the formula and nothing changed. So, I guess I have another problem I can't find.
I am up loading my file. I can't get the numbers to transfer correctly. My totals are off, I am only concerned for tab Aug 16, I think I can try to figure
out the rest of the year. I have placed what the totals should be in Line 213.

[MarvinP]

Hi Wskip49,

It looks like you are in need of a Pivot Table that groups dates by 7 days. I think you could do all your separate sheets using a single pivot. Look on the Response data sheet in Column S where I've created a single pivot. If you GROUP the rows as Day and count of 7 then you can get the whole week. You could also GROUP the dates as Months you can get a monthly report. You could even put the School Name as a filter and filter your data by school. I think it is time to learn more about Pivot Tables and grouping by dates.

PT by 7 days.xlsm

Watch http://www.bing.com/videos/search?q=...F0FD&FORM=VIRE
or
http://www.contextures.com/xlPivot07.html

[sanram]

As far as I can remember that I wrote this formula for the month of September 2016 and it was pulling data as you expected. Now what is the reason that you need to change it?

[Wskip49]

Thanks I'll try it

[sanram]

Generally Weekday(Date) should act as you wanted. But if it fails then try Weekday(Date,1).