Excel: Rolling 3 month calendar

Microsoft Office excel 2003
October 18, 2010 at 07:07:57
Specs: Windows XP, Dell Optiplex 745
I am trying to create a calender that tracks employee's vacation days for three months at a time. So, I want dates on top and employee names on the side. I only want to show this month and two months after at a time. After this month is over it should hide and then show the next three months. Any thoughts?

See More: Excel: Rolling 3 month calendar

Report •

#1
October 18, 2010 at 09:43:26
re: I want dates on top and employee names on the side

What's the detail as far as the dates?

Do you have a column for every day (i.e. ~90 columns)? 1 column per week? Per month? Something else?


Report •

#2
October 18, 2010 at 09:47:52
I have a column for each day. 10/1/10, 10/2/10, etc... I'd actually prefer it to have a merged cell above it saying, "Week 1", "Week 2", and so on, but it is not necessary. I know I will run out of columns for a whole year, so I may need to break it up for one half per tab or something like that...

Report •

#3
October 18, 2010 at 10:37:45
Does your vacation calender extend into the next year?

i.e. If today was November 1, would you want to view November 2010, December 2010 and January, 2011?


Report •

Related Solutions

#4
October 18, 2010 at 10:43:14
It would be best if it could run off of a fiscal calendar. For this purpose, the fiscal year 2011 starts on mar-29-10. So, I could either do it by the 3 months or do it by 13 weeks (even though as the calendar rolls it might show more than one quarter, but will always still be 13 weeks).

Report •

#5
October 18, 2010 at 12:12:38
Try this code.

What it does is:

1 - Hide all columns except for the names.
2 - Search for the first day of the current month and note which column it is in.
3 - Search for the first day of the 4th month from now (since months can have different end dates) and then move back 1 column to the end of the 3rd month and note which column it is in.
4 - Use the saved column numbers to unhide 3 months worth of dates.

Sub ThreeMonthView()
'Hide all column except for Names
 Sheets(1).Range(Columns(2), Columns(Columns.Count)).EntireColumn.Hidden = True
'Find first day of Current month
     Set curMonth = Sheets(1).Rows(1).Find(Month(Now) & "/1/" & Year(Now))
'Save Column Number
       curMonthCol = curMonth.Column
'Find first day of 4th month from now
     Set endMonth = Sheets(1).Rows(1).Find(Month(eomonth(Now, 2) + 1) & "/1/" & _
                                            Year(eomonth(Now, 2) + 1))
'Move back 1 column to last day of 3rd month & Save Column number
       endMonthCol = endMonth.Column - 1
'Unhide 3 months worth of dates
  Sheets(1).Range(Columns(curMonthCol), _
                  Columns(endMonthCol)).EntireColumn.Hidden = False
End Sub


Report •

#6
October 19, 2010 at 06:38:36
Thank you very much for the code. However, when I run it is says that sub or function 'eomonth' is not defined. Is this a typo?

Report •

#7
October 19, 2010 at 07:53:27
No, it's not a typo.

You'll probably need to add a reference to the Analysis Toolpak to make the EoMonth Function available to VBA.

First, in Excel, use Tools...Add-ins and choose both Analysis ToolPak and Analysis ToolPak - VBA.

Next in VBA, use Tool...References and check atpvbaen.xls

I don't believe that you'll see atpvbaen.xls under Tool...References in the VBA editor until you set up the Add-Ins in Excel.


Report •

#8
October 19, 2010 at 08:29:17
OK, I got that to work... But, now it gives me a runtime error. When I debug it finds the error at
Set endMonth = Sheets(1).Rows(1).Find(Month(eomonth(Now, 2) + 1) & "/1/" & _
                                            Year(eomonth(Now, 2) + 1))


Report •

#9
October 19, 2010 at 11:24:28
I see no reason for it to be throwing up an error on that particular line.

In fact I just copied that line, all by itself and put it in macro and it didn't return an error.

Sub TestTheLine()
 Set endMonth = Sheets(1).Rows(1).Find(Month(eomonth(Now, 2) + 1) & "/1/" & _
                                            Year(eomonth(Now, 2) + 1))
End Sub

Granted, it didn't find what it was looking for, so endMonth = Nothing, but it didn't throw up an error, nor would I have expected it to.

Are you sure that's where it's throwing up the error?

There are multiple types of run-time errors. What does the text of the error say?


Report •

Ask Question