|
VBA to populate workdat between dates
Hello,
I am hoping someone can help me. The problem is I am trying to get a VBA code that will request the user to input two dates and will populate the workdays between these dates into a specified column User Inputs StartDate = 01-Mar-2006 EndDate = 01-Mar-2007 Output in say column C 01-Mar-2006 02-Mar-2006 03-Mar-2006 06-Mar-2006 07-Mar-2006 08-Mar-2006 09-Mar-2006 10-Mar-2006 13-Mar-2006 14-Mar-2006 15-Mar-2006 etc Thanks in Advance |
VBA to populate workdat between dates
This will give you M-F without holidays.
Sub betweenDates() Dim StartDate As Date Dim EndDate As Date Dim CurDate As Date Dim aWS As Worksheet Set aWS = ActiveSheet StartDate = InputBox("Enter Start Date: ", Start) EndDate = InputBox("Enter End Date: ", Last) CurDate = StartDate lrow = 1 aWS.Cells(lrow, 1).Value = "Start Date: " & Format(StartDate, "mm/dd/yyyy") lrow = lrow + 1 aWS.Cells(lrow, 1).Value = "End Date: " & Format(EndDate, "mm/dd/yyyy") lrow = lrow + 2 Do While CurDate = StartDate And CurDate <= EndDate aWS.Cells(lrow, 1).Value = Format(CurDate, "mm/dd/yyyy") Debug.Print Weekday(CurDate, vbSunday) If Weekday(CurDate, vbSunday) = 5 Then CurDate = CurDate + 3 Else CurDate = CurDate + 1 End If lrow = lrow + 1 Loop End Sub HTH, Barb Reinhardt "fLiPMoDĀ£" wrote: Hello, I am hoping someone can help me. The problem is I am trying to get a VBA code that will request the user to input two dates and will populate the workdays between these dates into a specified column User Inputs StartDate = 01-Mar-2006 EndDate = 01-Mar-2007 Output in say column C 01-Mar-2006 02-Mar-2006 03-Mar-2006 06-Mar-2006 07-Mar-2006 08-Mar-2006 09-Mar-2006 10-Mar-2006 13-Mar-2006 14-Mar-2006 15-Mar-2006 etc Thanks in Advance |
VBA to populate workdat between dates
Hello Barb,
Fantastic! works like a gem! Thanks again! "Barb Reinhardt" wrote in message ... This will give you M-F without holidays. Sub betweenDates() Dim StartDate As Date Dim EndDate As Date Dim CurDate As Date Dim aWS As Worksheet Set aWS = ActiveSheet StartDate = InputBox("Enter Start Date: ", Start) EndDate = InputBox("Enter End Date: ", Last) CurDate = StartDate lrow = 1 aWS.Cells(lrow, 1).Value = "Start Date: " & Format(StartDate, "mm/dd/yyyy") lrow = lrow + 1 aWS.Cells(lrow, 1).Value = "End Date: " & Format(EndDate, "mm/dd/yyyy") lrow = lrow + 2 Do While CurDate = StartDate And CurDate <= EndDate aWS.Cells(lrow, 1).Value = Format(CurDate, "mm/dd/yyyy") Debug.Print Weekday(CurDate, vbSunday) If Weekday(CurDate, vbSunday) = 5 Then CurDate = CurDate + 3 Else CurDate = CurDate + 1 End If lrow = lrow + 1 Loop End Sub HTH, Barb Reinhardt "fLiPMoD£" wrote: Hello, I am hoping someone can help me. The problem is I am trying to get a VBA code that will request the user to input two dates and will populate the workdays between these dates into a specified column User Inputs StartDate = 01-Mar-2006 EndDate = 01-Mar-2007 Output in say column C 01-Mar-2006 02-Mar-2006 03-Mar-2006 06-Mar-2006 07-Mar-2006 08-Mar-2006 09-Mar-2006 10-Mar-2006 13-Mar-2006 14-Mar-2006 15-Mar-2006 etc Thanks in Advance |
| All times are GMT +1. The time now is 05:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com