Weekdays

[Guest]

Hi!

I have a macro that reads two dates from a worksheet, one start date and one
end date. I then run a long macro based on these dates that preforms
calculations for each date starting with the start date and ending with the
end date. This works fine. Now the problem is that the macro does this for
all days, including saturdays and sundays. I only want to do calculations for
weekdays. How can I solve this? Here is my code so:

'Read startdate and enddate
Dim datStartdate As Date
datStartdate = Application.ThisWorkbook.Sheets("Settings").Cells(6, 3)

Dim datEndDate As Date
datEndDate = Application.ThisWorkbook.Sheets("Settings").Cells(7, 3)

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
datDate = datDate + 1
Loop

Any help appreciated! Thanks alot!

 

[paul.robinson]

Hi!

I have a macro that reads two dates from a worksheet, one start date and one
end date. I then run a long macro based on these dates that preforms
calculations for each date starting with the start date and ending with the
end date. This works fine. Now the problem is that the macro does this for
all days, including saturdays and sundays. I only want to do calculations for
weekdays. How can I solve this? Here is my code so:

'Read startdate and enddate
Dim datStartdate As Date
datStartdate = Application.ThisWorkbook.Sheets("Settings").Cells(6, 3)

Dim datEndDate As Date
datEndDate = Application.ThisWorkbook.Sheets("Settings").Cells(7, 3)

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
datDate = datDate + 1
Loop

Any help appreciated! Thanks alot!

Hi
Try this in your loop. Weekday(datDate,2) is an excel function and
returns 1 (Monday) to 7 (Sunday).

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
With Application.Worksheetfunction
Do While datDate <= datEndDate
if .Weekday(datDate,2) = 6 or .Weekday(datDate,2) = 7 then
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
end if
datDate = datDate + 1
Loop


regards
Paul

 

[Rick Rothstein \(MVP - VB\)]

Try using this "Start loop" instead of the one you have...

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
If Weekday(datDate, vbMonday) < 6 Then
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
datDate = datDate + 1
End If
Loop

Rick

 

[Bob Phillips]

'Read startdate and enddate
Dim datStartdate As Date
datStartdate = Application.ThisWorkbook.Sheets("Settings").Cells(6, 3)

Dim datEndDate As Date
datEndDate = Application.ThisWorkbook.Sheets("Settings").Cells(7, 3)

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
If Weekday(datDate, 2) < 6 Then
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
End If
datDate = datDate + 1
Loop


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan]

Hi!

I have a macro that reads two dates from a worksheet, one start date and one
end date. I then run a long macro based on these dates that preforms
calculations for each date starting with the start date and ending with the
end date. This works fine. Now the problem is that the macro does this for
all days, including saturdays and sundays. I only want to do calculations for
weekdays. How can I solve this? Here is my code so:

'Read startdate and enddate
Dim datStartdate As Date
datStartdate = Application.ThisWorkbook.Sheets("Settings").Cells(6, 3)

Dim datEndDate As Date
datEndDate = Application.ThisWorkbook.Sheets("Settings").Cells(7, 3)

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
datDate = datDate + 1
Loop

Any help appreciated! Thanks alot!

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
if Worksheetfunction.Weekday(datDate,2) < 6 or then
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
end if
datDate = datDate + 1
Loop

 

[trevosef]

Hi!

I have a macro that reads two dates from a worksheet, one start date and one
end date. I then run a long macro based on these dates that preforms
calculations for each date starting with the start date and ending with the
end date. This works fine. Now the problem is that the macro does this for
all days, including saturdays and sundays. I only want to do calculations for
weekdays. How can I solve this? Here is my code so:

'Read startdate and enddate
Dim datStartdate As Date
datStartdate = Application.ThisWorkbook.Sheets("Settings").Cells(6, 3)

Dim datEndDate As Date
datEndDate = Application.ThisWorkbook.Sheets("Settings").Cells(7, 3)

'Start loop
Dim datDate As Date
datDate = datStartdate
Dim strDate As String
Do While datDate <= datEndDate
strDate = CStr(Format(datDate, "yyyymmdd"))
Call RunUpdate(strDate)
datDate = datDate + 1
Loop

Any help appreciated! Thanks alot!

Hi Arne,

You can use the WeekDay function to determine working days. Try this;
Dim iWeekDay As Integer

iWeekDay = WeekDay(datDate, vbMondday) 'Monday will be 1, ... Sat =
6 and Sun = 7

Before you call RunUpdate add a If condition;

If iWeekDay < 6 Then 'check that its Monday to Friday
Call RunUpdate(strDate)
End If
datDate = datDate + 1

Regards
trevosef