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


0
5/17/2007 10:29:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
411 Views

Similar Articles

[PageSpeed] 31

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
> 
> 
> 
0
5/17/2007 11:36:01 PM
Hello Barb,

Fantastic! works like a gem!

Thanks again!

"Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
news:DFC93B93-E26F-45A1-92F3-9BE0C52E2710@microsoft.com...
> 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
> >
> >
> >


0
5/18/2007 12:48:41 AM
Reply:

Similar Artilces:

Updating custom date format
Is there a way of doing a global update of a custom date format? The issue is thus: * We have a set of data with a particular date format, but we need to convert to "dd/mm/yyyy h:mm" format for subsequent saving as a text file. * We can select the relevant column and change the format, but the chnage is not immediately reflected in the contents of the selected cells. * The only way we have found to make the change is to select *each* cell individually and press return. * This is a grossly inefficient way considering we have files that contain up to a hundred lines of data. I...

DATES #4
I am trying to perform a simple formula, but I can't figure it out. In cell A1, I have the date 02-01-2005. I would a formula in A3 to calculate the date 60 days from A1. For example A1 is 02-01-2005. A3 should be 04-01-2005 Thanks, =A1+60 -- Regards, Peo Sjoblom "jody" <jholland@ccs.state.ms.us> wrote in message news:%23kVTLw6DFHA.3032@TK2MSFTNGP12.phx.gbl... > I am trying to perform a simple formula, but I can't figure it out. In cell > A1, I have the date 02-01-2005. I would a formula in A3 to calculate the > date 60 days from A1. For example A1 is ...

Date Display on Report
I have read the many date display threads on this site but have a new question (at least I think it is new). I am running a report from a parameter query and am able to get the dates to display just fine, but I want to just get the month to display. I don't want the acutal dates from the parameter. I want it to display the Name of the month based on the dates from the parameter. Is there a way to do this? Format ([The Parameter],"mmmm") If the parameter is a reference to a form control Format([Forms]![The Form Name]![The control Name], "mmmm") Or if the cont...

Sum a calculated date/time field
When trying to sum the date time field I get a aggregate error that refers to the first field in the query that has nothing to do with the calculated field. I am able to convert it into integer format and subtract two date fields to get the total time. But when I try to sum the total time I get the aggregate error refering to the first field in query. This is the calculation I use to get the field I would like to sum. It works great but can not figure out how to sum it without the aggregate error. Elapsed Time: Int(CSng(([actual completion time]-[actual start time])*24)) & ":...

date format...
Hi all Excamble: In the cell: Mon Mar 1, 2010 9:21:48 AM looks in formula bar --> Mon Mar 1, 2010 9:21:48 AM. Cell format is general. How I change this to 3.1.2010? ...

sorting date " MMM-YY" (Jan-05)
Hi, I have a spreadsheets with ticket numbers and the date it was submitted. It is in medium date format (3/18/2005). I have used this formula: =text(A2,"MMM-YY") to conver it to Mar-05. now i have a column with MMMYYY date, however, it will not let me sort it. It is sorting alphabetically, and not in the order of the date. so I have Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then year? Thank you! It is sorting alphabetically because the data is in TEXT format. In order to sort by date, you'll need to have the data formatted in some kind o...

freeze date stamp
hello, i am trying to use the now() function in such a way that once the workbook has been saved, the time stamp freezes. i am not successful. is there another way? thanks, mac. --- Message posted from http://www.ExcelForum.com/ You can just use CTRL+; and it will insert the date for you, but to do as you were trying you need VBA in the Before_Close event of the ThisWorkbook module to insert the date as a value. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ------------------------------------------...

Data Validation enter values with VBA
I currently have a Spreadsheet running a tipping competition which works well, however would like to add additional functionality to it. I have cells setup which are data validated to a list on the sheet and have four values to select from which are None, Team 1, Draw, Team 2 These come up in a drop down box. What I would like to be able to do is to have the data filled automatically in a group of cells dependent on the value in another cell. For example In Cell A10 i put in H and Cell F10 which has the data validated list always has the value None to start with the l...

Emailing from Excel based on date selection by row
Hi I need help with this code which should check all filled rows in the sheet and compare the date in D(i) with todays date in "H1" and if = send an email. The email part works OK and the code was taken from a forum . But the parameters MailSubj1 and Mailsubj2 values are not passed to the SendNotesMail subroutine. Can anyone help with this? Here is the code: ------------------------- ooo ------------------------------ Sub checkdate() Dim Ws As Worksheet Dim oRow As Long Dim Mailsubj1 As String Dim Mailsubj2 As String Set Ws = ThisWorkbook.Worksheets(&...

BOM and Effective Dates
Hi, We are thinking of using GP's BOM module (not manufacturing) to address some volatile BOM requirements. The client has very dynamic BOM's which may change its components every few months. Currently we are thinking of using effective dates, but realised that apart from making such items to sub assembly the options are limited in getting them defaulted properly to Assembly Entry. i.e. if we define multiple components for the same bill, (say C1, C2, C3) with effective and obsolete dates, (C1-for 2005, C2-for 2006 C3 for 2007) and we would like to get the correct mix to appear ...

temporary file names via Get-Date
The "PowerTip of the Day" article at http://app.en25.com/e/es.aspx?s=1403&e=2867&elq=8322eea60ce14904acb144f2ae454b60 displayed this code to generate temporary file names PS C:> (Get-Date -format 'yyyy-MM-dd hh-mm-ss') + '.tmp' 2010-03-18 09-48-08.tmp I recoded it to do it this way PS C:> (Get-Date).Ticks.ToString() + '.tmp' 634045027709549764.tmp Now suppose I later wanted to turn that "634045027709549764" back into a legible date-time expression like "2010-03-18 09-48-08". Is that possible? ...

Address ID Card Modify Date
I was looking in the RM00102 table and noticed there is a column titled MODIFDT, but also noticed that every record has a date of 1/1/1900 (essentially a blank date). Does anyone have any idea how to activate this field to update every time an address is modified? TIA GP7.5 MSSQL WIN2KPro A) You could add some VB code to the Address form to set the MODIFDT date on [Save]. I haven't tried adding vb code for awhile. B) You could create an update trigger on the table to update MODIFDT . That is risky as nobody knows what order updates occur in Great Plains does when you click [S...

pivottable does not sort date values?
Anyone know why excel 2000 does not sort date values? it lists in the following way: 01-2001 01-2002 01-2003 02-2001.... or=20 2001-01 2001-10 2001-11 ..=B7.. Yes. Your dates have been input as text. It is simply=20 sorting them by alphabetical order. Go back to your original data and find the column with the=20 dates. Create a new column and use =3Ddatevalue(a1) where=20 a1 is the reference to the cells with the misinterpreted=20 dates. Display the cells in the new column as dates. Now recreate your pivot table including this new column=20 and you will find you will be able to sort i...

What VBA function returns cell 2 from which cell 1 gets its value?
This is a simple and possibly embarrassing question. In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on a different sheet. Cell 1 has a UDF, so it looks like: "=MassageData(SalesSheet!$A$1)" Given that I know cell 1 at runtime, what VBA function returns cell 2? I have tried: Set Cel2 = Cel1.Precedents(1) Set Cel2 = Cel1.Precedents.Cells(1, 1) etc. but that only seems to return Cel1 itself (at least that's what the Debug Window shows). 1) Is my syntax wrong? 2) Is Excel's lack of external dependent/precedent functionality in my...

Need Date field in a form to stay same until changed
How can I get a date or any field to stay the same in a form until I change it. I need to post a lot records under same date. It would save untold time if I could not have to re-enter the date each time. Thanks for any help. On Mon, 1 Oct 2007 12:59:02 -0700, Pat wrote: > How can I get a date or any field to stay the same in a form until I change > it. I need to post a lot records under same date. It would save untold time > if I could not have to re-enter the date each time. > > Thanks for any help. If the field is NOT a Date datatype code that control's AfterU...

Auto_Open update VBA
Hi all, On Auto_Open the appplication requests user to enter a serial number through an Input Box. I need the user's entry to update a Globals Public Const value so that each time the application is opened thereafter a validation of this constant is made. If entries do not mathch application closes automatically. Question is how do I update the Public Cons in the VBA Editor from the entry made by user in the Input Box. Thank you ...

Display Database Modified Date on form
I would like to display the date that the database was last modified on a form. I found a message in the newsgroup (I can't seem to find it again) that suggested this code: Dim fs As Object Dim f As Object Dim strModDate As String Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile(CurrentProject.FullName) strModDate = f.DateLastModified Debug.Print strModDate Set f = Nothing Set fs = Nothing However, I need a little more direction. Can someone please provide some basic assistance? I'm assuming that the code is assigne...

dates and formulae
Hi Can i put in a formula to subtract one date from another and show the period between in days, weekks or whatever. It is to calculate someones service with the company. It is many different periods spanning 30 years. thanks Gary Take a look at Chip Pearson's explanation of =datedif() at: http://www.cpearson.com/excel/datedif.htm geepeetee wrote: > > Hi > > Can i put in a formula to subtract one date from another and show the period > between in days, weekks or whatever. It is to calculate someones service > with the company. It is many different periods spa...

Populate cell if another cell is a certain weekday
Hi all, I would like help with the following: A1 : Start Date A2 : End Date A3 : $500 I would like to populate cells A4 to A34 with dates between and including start date (A1) and end date (A2). Then cells between B4 to B34 should only populate with the value in A3 if the date in cells A4 to A34 is a Thursday. Thank you in advance for your help. In A1: 8/7/2007 In A2: 9/6/2007 In A4: =A1 In A5: =A4+1 and copy down In B4: =IF(WEEKDAY(A4)=5,$A$3,"") and copy down. You should see: 8/7/2007 9/6/2007 500 8/7/2007 8/8/2007 8/9/2007 500 8/10/2007 8/11/2007 8/12/2007 8/13/...

vba code for saving file
Hi, I want to create a button that save (saveas) the workbook where the filename should be the string thats in cell B2 Anyone? txs One way: Public Sub CommandButton1_Click() Const ERRSTR As String = "File not saved." & _ vbNewLine & vbNewLine Dim fName As String On Error GoTo Handler With ActiveWorkbook fName = .Sheets("Sheet1").Range("B2").Text If Len(Trim(fName)) = 0 Then _ Err.Raise 32769 If Mid(fName, Len(fName) - 3, 1) <> "." T...

restict access to vba from worksheet
i need to restict access to vba from workshee -- Message posted from http://www.ExcelForum.com To protect the VBA project, from your workbook right-click the workbook's icon and pick View Code. This icon is to the left of the "File" menu this will open the VBA editor, in the left hand window right click on your workbook name and select VBA project properties, protection, check lock project for viewing and set a password. Press Alt and Q to close this window and go back to your workbook and save and close the file Be aware that this password can be hacked with 3rd party softwar...

GL posting date is zero when posting inventory trans-variance
Hi, Item Transaction Entry, document type="Variance", posting by batch. The posting is set to post using batch date. Posts to GL with a zero date. This worked fine until August 2007, no apparent changes have been made to the system. Great Plains version 8.00g7 (8.0 with no SP applied). Has anyone encountered this problem? Thanks. Did anyone change the Posting settings for this origin? If so you will run into this if you created a batch before the change and used that batch after the change. The Posting Date will be blank. (00/00/00) "Glen" wrote: > Hi, > ...

Want to be able to click on a cell and have a list box appear to give values to populate a cell
I have a excel sheet that has some cells that I want to populate from a list of fix values. Basicly I want to click on a cell and have a list box pop up that lists our departments and be able to click one of the departments and have that value populate the cell. What is the best way to do this? 1) Create the list of departments, perhaps on a separate worksheet you'll hide later. Optionally, you could name the list. 2) Select the cell 3) Choose Data | Validation... 4) In the Allow entry field, choose "List" 5) In the Source entry field, enter the range from step 1, either by...

Calling a ReportWriter Report From VBA
Hi, Is there a way to run a Report Writer report (say, Purchase Order) from VBA? Thanx. You have a few options, what exactly are you trying to build? "GeeeP" wrote: > Hi, > > Is there a way to run a Report Writer report (say, Purchase Order) from VBA? > > Thanx. You can add custom logic using VbA when the report is ran. I doubt there is direct way of calling the report thru Vba passing parameters to it. On Aug 5, 1:22 pm, Leslie Vail <LeslieV...@discussions.microsoft.com> wrote: > You have a few options, what exactly are you trying to build? > >...

VBA
How to copy and paste an Excel Table (obviously from an open Excel Spreadsheet) without bringing over the Gridlines / comments etc? how would I do this in VBA automatically? thanks, Amrit ...