Work Week dates

  • Thread starter Thread starter GerryK
  • Start date Start date
G

GerryK

Hi,
Is there a procedure to populate column A (starting at A2)
with work weeks(being Mon. to Fri.)date parameters for a
year by checking the year in A1?

If in A1 I have 2004
In A2 I would like to see:
January 1 - January 2

In A3
January 5 - January 9

In A4
January 12 - January 16
etc.

Thanks for any help.
 
Gerry,

Here's one way

A2: ="January 1 - " & TEXT(DATE($A$1,1,1)+(6-WEEKDAY(DATE($A$1,1,1))),"mmmm
d")
A3: =TEXT(DATE($A$1,1,1)+(6-WEEKDAY(DATE($A$1,1,1)))+(ROW()-2)*7-4,"mmmm
d")&" - "&TEXT(DATE($A$1,1,1)+(6-WEEKDAY(DATE($A$1,1,1)))+(ROW()-2)*7,"mmmm
d")

copy A3 down, but watch for wrap-around on that formula

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Gerry,

Here's a macro (below): put the year into cell A1, then run the macro.
Not sure what you wanted to do if your week ended up being one day -
(Jan1 on a Friday, or Dec 31 on a Monday) so I did Jan1-Jan1 and
Dec31-Dec31 for those cases.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myDate As Date
Dim myYear As Integer
Dim StartDate As Date
Dim EndDate As Date
Dim WeekString As String
Dim dayString As String

StartDate = DateValue("1/1/" & Range("A1").Value)
EndDate = DateValue("12/31/" & Range("A1").Value)


For myDate = StartDate To EndDate
dayString = Format(myDate, "ddd")
If dayString <> "Sat" And dayString <> "Sun" Then
If WeekString = "" Then
WeekString = Format(myDate, "mmmm d") & " - "
End If
If dayString = "Fri" Then
WeekString = WeekString & Format(myDate, "mmmm d")
Range("A500").End(xlUp)(2).Value = WeekString
WeekString = ""
End If
End If
Next myDate
If WeekString <> "" Then
WeekString = WeekString & Format(EndDate, "mmmm d")
Range("A500").End(xlUp)(2).Value = WeekString
End If

End Sub
 
Bob (and Gerry),

Note that this returns incorrect results if January 1 is on Saturday
or Sunday.

The formula requires a conditional for the "January 1 - " part of the
formula.

HTH,
Bernie
MS Excel MVP
 
That becomes too horrible to contemplate. Stick with your macro methinks.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top