| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto-populate other cells based on dates | Excel Discussion (Misc queries) | |||
| auto populate dates | Excel Worksheet Functions | |||
| VBA to populate workdat between dates | Excel Discussion (Misc queries) | |||
| Auto populate dates | Excel Discussion (Misc queries) | |||
| Cells populate on specific dates | Excel Worksheet Functions | |||
Linear Mode
