Solved I need a 30 day rolling calendar

November 3, 2018 at 12:30:50
Specs: Android
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.

See More: I need a 30 day rolling calendar

Reply ↓  Report •

✔ Best Answer
November 3, 2018 at 21:59:30
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

http://www.skeptic.com/

message edited by mmcconaghy



#1
November 3, 2018 at 13:40:08
Maybe you can do something with this.
https://www.timeanddate.com/calenda...

See if anything here suits.
https://mobile.softpedia.com/dyn-se...


Reply ↓  Report •

#2
November 3, 2018 at 14:44:17
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


Reply ↓  Report •

#3
November 3, 2018 at 14:53:05
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


Reply ↓  Report •

Related Solutions

#4
November 3, 2018 at 16:37:55
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.

Reply ↓  Report •

#5
November 3, 2018 at 19:09:31
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, 2018         

Drag 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 the View Tab
Select Freeze Panes in the Window section
Select Freeze Top Row

Next were going to do some Conditional Formatting to Highlight the last 30 days.

1) Select your range of cells: A1 thru A366
2) Select the Home tab
3) On the ribbon click Conditional Formatting
4) Click on New Rules, it’s near the bottom of the dialog box.
5) Click Use Formula to determine which cells to format.
6) Enter the formula:

=AND($A2>=TODAY()-28,$A2<=TODAY()+1)

7) Click on the Format button
8) Select the Fill Tab
9) Select a pretty color
10) Click OK
11) Click OK

The previous 30 days should now be highlighted.

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

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#6
November 3, 2018 at 19:30:22
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


Reply ↓  Report •

#7
November 3, 2018 at 19:56:43
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.

Reply ↓  Report •

#8
November 3, 2018 at 21:59:30
✔ 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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#9
November 3, 2018 at 22:33:59
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.

Reply ↓  Report •

#10
November 5, 2018 at 14:53:53
Nice work folks, well done all.

Reply ↓  Report •

Ask Question