Work Week dates

[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.

 

[Bob Phillips]

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)

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

 

[Bob Phillips]

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)

 

[GerryK]

With great appreciation Bob and Bernie!!!
Thank you ever so much!
Gerry