+ Reply to Thread
Results 1 to 4 of 4

Code involving start and end dates

Hybrid View

  1. #1
    Registered User
    Join Date
    10-15-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    29

    Code involving start and end dates

    Good day, Gurus

    I am working on the attached Workbook. This forum helped me with transferring data from one sheet to another but now I have created myself a new problem. My "Information" tab has 2 this 2 rows named: "Leave Start Date" and "Leave End Date." What I want to do is, if I enter a range of dates between the 2 rows, Excel will take all the working days between those dates (minus weekends) and list them one row after the other in my "Table" worksheet. I want to use the data available in the "Table" worksheet to create a dashboard later on. I have attached a sample Workbook for your reference.

    Much thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Code involving start and end dates

    Just spotted this thread - as it is allied to your previous query keep all in one thread as it makes it easier for in-context advice to be made.
    It is late at night in the U.K. - I will submit a more useful solution tomorrow.
    For your recording you have chosen to use a 'structured table' and allocated 9,000 rows, this is unnecessary if your next entry goes to the row immediately following the filled table the table will expand dynamically.
    Explore the use of a bespoke VBA UserForm for your initial data entry you will find it far simpler then to contain your code rather than using macros on buttons.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,962

    Re: Code involving start and end dates

    Regarding the comment above, I don't know what the status of your project is, but...anyway....

    Option Explicit
    Sub Transfer()
        Dim LvStDt      As Date
        Dim LvEnDt      As Date
        Dim LvDays      As Long
        Dim DestRow     As Long
        Dim NewRec      As Variant
        Dim NewItem     As ListRow
        Dim NxtDay      As Long
            
        With Worksheets("information")
            LvStDt = .Range("d4").Value
            LvEnDt = .Range("d5").Value
            LvDays = LvEnDt - LvStDt
            NewRec = Application.Transpose(.Range("D2:D4").Value)
            .Range("D2:D5").ClearContents
        End With
            
        With Worksheets("Table").ListObjects(1)
            For DestRow = 0 To LvDays
                NewRec(3) = DateValue(LvStDt) + DestRow
                If WorksheetFunction.Weekday(NewRec(3), 2) < 6 Then
                    Set NewItem = .ListRows.Add
                    NewItem.Range.Value = NewRec
                End If
            Next DestRow
        End With
    End Sub
    Last edited by protonLeah; 09-05-2024 at 07:52 PM.
    Ben Van Johnson

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Code involving start and end dates

    VBA/UserForm approach - note there are no on-sheet formula or data validation the process is dealt with totally within the userform code.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 09-08-2016, 07:00 PM
  2. [SOLVED] 3 way look up involving dates.
    By Raanan in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 09-17-2013, 07:02 AM
  3. Replies: 9
    Last Post: 03-05-2012, 08:17 AM
  4. Calculations involving dates...
    By Finalfrontier1976 in forum Excel General
    Replies: 2
    Last Post: 07-13-2011, 07:40 AM
  5. Replies: 5
    Last Post: 06-30-2011, 03:26 PM
  6. Need help with formula involving dates
    By dataslinger in forum Excel General
    Replies: 2
    Last Post: 03-16-2010, 11:57 AM
  7. If Formulas involving dates
    By patelh9 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-09-2009, 02:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1