Re: Shifting Weeks for Quarterly Reports
From: Myrna Larson (anonymous_at_discussions.microsoft.com)
Date: 09/08/04
- Next message: JulieD: "Re: COUNTIF"
- Previous message: Ron Rosenfeld: "Re: cell format problem"
- In reply to: setoFairfax: "Shifting Weeks for Quarterly Reports"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 08 Sep 2004 11:43:13 -0500
Are you writing VBA code, or just designing a worksheet with formulas?
If the former, the VBA routines at the bottom of my reply may help. They
assume the weekdays are numbered 1-7, with Sunday = 1, Monday = 2, Friday = 6.
To get the first Friday *AFTER* 5/1/2004, the formula is
=NextDayOfWeek(DATE(2004,5,1),6)
For the first Friday *ON or AFTER* 5/1/2004,
=NextDayOfWeek(DATE(2004,5,0),6)
(the 0th day of May is the 30th of April).
For the *third* Friday of May, 2004, the formula is
=NthWeekday(2004,5,6,3)
If you want to avoid VBA, I would create a lookup table that is a list of all
of the Fridays in the date range of interest. That's easy enough to do: put
the date of the first Friday in a cell, and in the cell below, the formula
=A1+7 and copy down as far as needed. You should be able to lookup the dates
for the 1st Friday on or after May 1. That has to be a date between 5/1 and
5/7, inclusive, so you can find it with a formula like this:
=VLOOKUP(DATE(2004,5,7),Fridays,1)
To get the 2nd Friday, lookup 5/14; to get the 3rd, 5/21, etc.
The VBA functions: Note that I used the slower DateAdd and DateDiff functions
instead of simple addition and subtraction so the routines will still work
even if the "under the hood" method of storing dates is changed.
Function NextDayOfWeek(Start As Date, DOW As Long) As Date
Dim d As Date
'back up to previous Saturday, then forward to correct DOW
d = DateAdd("d", -Weekday(Start) + DOW, Start)
'if result isn't later than Start, move to next week
If DateDiff("d", Start, d) <= 0 Then
d = DateAdd("ww", 1, d)
End If
NextDayOfWeek = d
End Function 'NextDayOfWeek
Function NthWeekDay(y As Long, m As Long, DOW As Long, _
Which As Long) As Date
Dim d As Date
d = DateSerial(y, m, 0)
d = NextDayOfWeek(d, DOW)
If Which > 1 Then d = DateAdd("ww", Which - 1, d)
NthWeekDay = d
End Function 'NthWeekday
On Wed, 8 Sep 2004 07:59:09 -0700, "setoFairfax"
<setoFairfax@discussions.microsoft.com> wrote:
>Hello,
> I am creating a program in Excel to generate quarterly reports. I
>already have a form template set up that the user would enter daily which
>outputs selected data to an Excel workbook database.
> I am now trying to get the information from the database into several
>reports. All that this does is display dollar amounts. The reports are
>weekly (M,T,W,Th,F), Monthly (by week), and Quarterly.
> The quarterly reports are easy - I can use a conditional sum statement to
>sum the entries that are in months 1, 2, and 3, or 4, 5, and 6, etc.
> The weekly and monthly reports are stumping me however.
> The biggest problem is with the shifting days and weeks as years change.
>May might span 6 weeks in 2004, but only 5 weeks in 2005.
> For the weekly reports, they must show the dollar totals for Monday
>through Friday of that week for a particular month. However, the first and
>last weeks of a month almost always have fewer than 5 weekdays. So I cannot
>make a conditional sum statement that totals M-F in Week 4 (or 5) for the
>last week of January. Furthermore, since the number of weeks in a month
>change from year to year, any statement I write for now may not work next
>year.
> The monthly reports suffer the same problem; the number of weeks in a
>month is not constant, so I cannot sum based on results from the weeknum( )
>function. The monthly reports are broken down by week and show week 1, week
>2, etc for the month.
> If anybody could please help me solve this problem I would greatly
>appreciate it! Thank you!
- Next message: JulieD: "Re: COUNTIF"
- Previous message: Ron Rosenfeld: "Re: cell format problem"
- In reply to: setoFairfax: "Shifting Weeks for Quarterly Reports"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
