Need help with a due date and quarterly based spreadsheet

[Robg264]

Good day,

I've been given the task at work to create a spreadsheet for our staff based on their caseload of patients. This spreadsheet will basically inform the staff when a quarterly report needs to be completed for the patient based on the start date. The report must be completed every 90 days (or 3 months) and we have to count weekends and holidays.

I'm using the Edate feature in my spreadsheet that gives me the 3 months based on the start date. However, as you will see with the attached file, I have 4 columns labeled QTR 1, QTR 2, QTR 3 and QTR4. I also have a filter at each column heading. One of the problems I have is that their asking me for a list of patients that are due for report for a particular month. Now if I go to the column QTR 1 and choose the appropriate filter I get my information. However, is there an easier way to tell the program to check QTR2, QTR3 and QTR4 as well without having to do it manually for each one?

Yet, for you Excel Gurus, is it possible just to have one column that will tell me when the report is due utilizing the computer's date? Secondly, we're told that if the date falls on a weekend or a holiday we have to do the report the day before. So when the computer generates the due dates and the dates falls on a weekend, holiday can a conditional format be setup to highlight those dates so we know in advanced we have to do it on a workday.

If I can get away with just one field that would be a blessing.

I uploaded the file to a hosting site for those that want to take a look.

http://www.megaupload.com/?d=UB1DIE0Y

 

[Steve=True]

You can use this formula to move your future date off of a weekend

=DATE(YEAR(G8),MONTH(G8)+3,DAY(G8))+IF(WEEKDAY(DATE(YEAR(G8),MONTH(G8)+3,DAY(G8)))=1,-2,IF(WEEKDAY(DATE(YEAR(G8),MONTH(G8)+3,DAY(G8)))=7,-1,0))

Not sure how to add a holiday component just yet.

Alternately there is a formula (Workday) where you can add a certain number of days to a start date and you can add your own holiday list for it to jump over. It also jumps over weekends.

=WORKDAY(G7,90-24,YourHolidayListasANamedRangeofDates)

However, since there are not an even number of weeks in 90 days (12.85), this formula isn't always right.

I am sure someone else can do this more elegantly and i would be interested in seeing how

 

[Robg264]

Well If the part with the holiday and workday can't be worked out that's fine, we'll just have to manually look at a calendar. Being that I have 4 columns in my sheet with various dates, is it possible to tell Excel to look in those 4 columns and tell me what reports are due for let's say June, 2011 without having to run the filter in each column?

 

[Steve=True]

=SUMPRODUCT(--(MONTH((DATE(YEAR(G8:G11),MONTH(G8:G11)+3,DAY(G8:G11))))=6*1))


Change the range to the range you want to look at.

Change the 6 to the month you want to look at.