Automatic total number of weeks

**khank** · 12-29-2010, 03:13 AM

I'm looking for a formula which tells me the total number of weeks within my records. I'm presently doing it "manually" and I would like it automatically update itself as a week finishes.

All in the name of "streamlining" and elimination of non-essential columns.

On my "DAILY" sheet I have 1013 records (updated daily) along with current dates.

On my "SUMMARY" sheet I just have the grand totals: 1013 records to date and then I have the manually inputted 144 weeks which run from Thursday (4) through Wednesday (3).

This is where I want my weekly totals to automatically update.

Any solutions?

**teylyn** · 12-29-2010, 04:05 AM

Hello,

a data sample would sure help.

Will any given date only appear once?
Will there be gaps between dates?

If there are no gaps and there is only one occurrence of each date, then you can simply divide the total number of records by 7 to arrive at 1013/7=144.7143

or, with a formula, and assuming that the dates are in column A of the Daily sheet

=int(count(Daily!$A:$A)/7)

How do you want to handle "incomplete" weeks?

Please provide a data sample (not your whole data dump, please) and mock up the desired results.

cheers,

**khank** · 12-29-2010, 05:03 PM

Attached is a sample file. As you'll see I incorporated your formula and it works just fine (Summary sheet). You'll also see the rudimentary formula I use to "count" days left within the current week (on the Daily sheet), on the Summary sheet I just pickup the total.

Yes I would like to "streamline" the calculation process instead of "manually" inputting the old formula every 7 days to get my "days-left" within the week.

**teylyn** · 12-31-2010, 12:13 AM

Hello,

see attached for some thoughts.

You could subtract the smallest date from the largest date and divide the result by 7 to arrive at the number of weeks. The integer of that number is the number of complete weeks entered.

=INT((MAX(B:B)-MIN(B:B)+1)/7)

The decimal part of that number multiplied by 7 is the number of days completed in the last, incomplete week.

=MOD((MAX(B:B)-MIN(B:B)+1)/7,1)*7

The days remaining in the current week is 7 minus the days completed.

=7-(MOD((MAX(B:B)-MIN(B:B)+1)/7,1)*7)

See attached. Since you are using IFERROR formulas in your sheet, I attach an xlsx file.

cheers