Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekdays
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekdays
On Oct 26, 9:04 am, Arne Hegefors
wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekdays
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 "Arne Hegefors" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekdays
'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) "Arne Hegefors" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekdays
On 26 Oct, 09:04, Arne Hegefors
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekdays
On Oct 26, 4:04 pm, Arne Hegefors
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekdays | Excel Discussion (Misc queries) | |||
Idenfying WeekDays | Excel Worksheet Functions | |||
Weekdays only! | Excel Discussion (Misc queries) | |||
just weekdays | Excel Programming | |||
Weekdays | Excel Discussion (Misc queries) |