Hi at 68 I'm not to clever with things like Excel unless quite basic. My problem at the moment living in my motorhome and based a lot of the time in Napier, NZ for part time work. Napier only allow you to stay in the 4 designated freedom camping sites for 2 consecutive days and any 10 days in a rolling 30 day period. I have tried everywhere to find a rolling calendar to track my 10 days (which are never 10 days consecutive) in a 30 day period. Can someone please help.

✔ Best Answer

Here is an updated formula. Replace the formula in cell C1 with this:

=COUNTA(INDEX(B:B,MATCH(TODAY(),A:A,0)):INDEX(B:B,MAX(1,MATCH(TODAY(),A:A,0)-29)))

The previous formula would SUM the numbers in column B

so, if by mistake you entered any number other than 1

your count of the days would be in error.This new formula Counts the number of non-empty cells

so, you can put any character you like or you could note

the site number and the count of days will be correct.MIKE

message edited by mmcconaghy

Maybe you can do something with this.

https://www.timeanddate.com/calenda...See if anything here suits.

https://mobile.softpedia.com/dyn-se...

Many thanks for your reply, time and date was one of the first I tried, but it doesn't appear to do a rolling days type, nor anything in softpedia. I can visualise an Excel layout which would have dates over a 30 day period in one column and nights stayed in the next column, with possibly a total at the bottom of that column, then each day the book was opened it would show only the previous 30 day period with nights stayed. But honestly have no idea how to do that. I have looked at some very complicated ideas on Utube, but they loose me almost straight away.

message edited by Marinernz

Since we don't know how you plan to enter the data in Excel, it's hard to give you a direct answer. With more details, we could be more specific, but this is one way to do it. Set up your sheet as follows. Just enter 1/1/2018 (or any date) in A2 and drag it down as far as you want. For any day that you want to count, put a 1 in Column B.

A B 1 Date Stay 2 1/1/2018 3 1/2/2018 4 1/3/2018 5 1/4/2018 1 6 1/5/2018 7 1/6/2018 8 1/7/2018 9 1/8/2018 10 1/9/2018 1 11 etc.In C31, which would be the same row as 1/30/2018 in my example, enter this formula and drag it down:

=SUM(INDIRECT(ADDRESS(ROW()-29,2)&":"&ADDRESS(ROW(),2)))

Breakdown:The ROW() function simply returns the Row number of the cell that the function is in.

The ADDRESS function uses a row number and column number to create a Text version of a cell reference.

The INDIRECT function takes a Text version of a cell reference (or reference to a range of cells) and turns it into an actual reference.

The SUM formula sums the range returned by the INDIRECT function.

In C31, you have:

=SUM(INDIRECT(ADDRESS(ROW()-29,2)&":"&ADDRESS(ROW(),2)))

=SUM(INDIRECT(ADDRESS((31-29),2)&":"&ADDRESS(31,2)))

=SUM(INDIRECT(ADDRESS(2,2)&":"&ADDRESS(31,2)))

=SUM(INDIRECT("$B$2&":"&"$B$31"))

=SUM(INDIRECT("$B$2:$B$31")

=SUM($B$2:$B$31)As you drag it down, the ROW() function increments by 1 each time, therefore you are always looking back 30 days.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Hi, many thanks for that, followed your instructions and works perfectly as described. I take it I have to also drag the dates down as well as the formula to see the next date I can park there, i.e. less than 10 in column C. I take it this doesn't actually roll, but must be manually changed for date and nights stayed.

This is a variation on DerbyDads solution.

I've added a few bells and whistles.With your data looking like:

A B C D 1) Date Stay 16 << Days 2) January 1, 2018 3) January 2, 2018 4) January 3, 2018 5) January 4, 2018 6) January 5, 2018 7) January 6, 2018 8) January 7, 2018 9) January 8, 2018 10) January 9, 2018 11) January 10, 2018 12) January 11, 2018 13) January 12, 2018 14) January 13, 2018 15) January 14, 2018 16) January 15, 2018Drag your Dates down for a complete Year.

In cell C1 enter the formula:

=SUM(INDEX(B:B,MATCH(TODAY(),A:A,0)):INDEX(B:B,MAX(1,MATCH(TODAY(),A:A,0)-29)))

As with DerbyDads formula, this will sum the values in column B for the last 30 days

Next we want to Freeze Row number One,

so when you scroll down, row one stays in place.

Select row one, by placing your cursor on the number 1

On the Ribbon, select theView Tab

SelectFreeze Panesin the Window section

SelectFreeze Top RowNext were going to do some Conditional Formatting to Highlight the last 30 days.

1) Select your range of cells: A1 thru A366

2) Select theHometab

3) On the ribbon clickConditional Formatting

4) Click onNew Rules, itâ€™s near the bottom of the dialog box.

5) ClickUse Formula to determine which cells to format.

6) Enter the formula:=AND($A2>=TODAY()-28,$A2<=TODAY()+1)

7) Click on the

Formatbutton

8) Select theFillTab

9) Select a pretty color

10) Click OK

11) Click OKThe previous 30 days should now be highlighted.

Have not test this very extensively, but seems to work.

MIKE

Rolling the dates is the easy part. Put this in A2 and drag it down to A31. =TODAY()-ROW()+2

Each day the TODAY() function will update to the current date and you'll see the current date in A2 and 29 "prior dates" in A3:A31.

The hard part is "rolling" the Stays and keeping them with dates as they change. Off the top of my head, the only way I can think to make that happen is with a macro. I could write one for you if that's OK.

There may be a formula way to roll the Stays but as of now I'm not seeing it.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thank you Mike, this does appear to do the trick nicely, will go with it for a few weeks and see how it goes. I believe the security guys for the council must have a pretty flash system as they simply photograph everyone's licence plate and they know 1. How many consecutive days at any one site and 2. If you exceed 10 days in any rolling 30 day period. They then fine you $200, two young girls got caught three nights ago.

Here is an updated formula. Replace the formula in cell C1 with this:

=COUNTA(INDEX(B:B,MATCH(TODAY(),A:A,0)):INDEX(B:B,MAX(1,MATCH(TODAY(),A:A,0)-29)))

The previous formula would SUM the numbers in column B

so, if by mistake you entered any number other than 1

your count of the days would be in error.This new formula Counts the number of non-empty cells

so, you can put any character you like or you could note

the site number and the count of days will be correct.MIKE

message edited by mmcconaghy

Hi Mike, this is great thanks, I'll run this for a while and see how it goes, when I showed this to my wife (who is actually worse than me with technology) she suddenly realised what a rolling period meant. So hopefully no fines for us.

Nice work folks, well done all.

Ask Your Question

Weekly Poll

Do you think Google's new Pixel 4 will gain traction?

Discuss in The Lounge

Poll History