+ Reply to Thread
Results 1 to 12 of 12

Auto populate dates based on date range

  1. #1
    Registered User
    Join Date
    01-02-2018
    Location
    VA
    MS-Off Ver
    Excel
    Posts
    8

    Auto populate dates based on date range

    I am currently trying to implement the following:
    Specify a date range for a period of time eg. 1/2/18 - 1/14/18 in cell "I4" that will be entered on my main worksheet.

    I would like that entry to then populate on a separate page of the same worksheet, each date between the entered range.
    i.e. 1/2/18, 1/3/18, 1/4/18 etc etc ending on 1/14/18. This will then be used to track daily labor hours. These entries would begin in cell K2-K38 allowing for longer project time tracking.

    Any ideas or if this is possible?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Auto populate dates based on date range

    Yes it's possible. Though I'd suggest splitting start and end date into 2 separate cells for ease of calculation.

    Let's say that you have 1/2/18 in I4 & 1/14/18 in I5.
    Then K2 formula is.
    =IF(($I$4+ROW(A1)-1)<=$I$5,$I$4+ROW(A1)-1,"")

    Copy down.

    If it's in same cell, you need additional operation to convert text date into workable date value.
    Ex: You need to replace $I$4 in above with below.
    TRIM(LEFT(SUBSTITUTE($I$4," ",REPT(" ",50)),50))
    $I$5 with...
    TRIM(RIGHT(SUBSTITUTE($I$4," ",REPT(" ",50)),50))+0
    Attached Files Attached Files
    Last edited by CK76; 01-03-2018 at 10:34 AM. Reason: Attached sample file.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    01-02-2018
    Location
    VA
    MS-Off Ver
    Excel
    Posts
    8

    Re: Auto populate dates based on date range

    Ok, so I have tried it based on the first IF statement however I am running into some issues.

    Here is how I have it setup currently and have implemented two separate cells to enter the date range.

    "PM Data" is worksheet with date range specified in cells H12 & I12.
    "Daily Hours" is worksheet with date ranges needed to populate from "PM Data worksheet" starting in cell K2 - K38.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Auto populate dates based on date range

    So what's the issue? I'd recommend uploading sample workbook with just the portion you need to demonstrate your problem.
    It makes troubleshooting so much easier.

    To upload, go to "Go Advanced" found at bottom of "Edit" or "Quick Reply" menu.
    Find "Manage Attachment" link and click. It will open new tab/window to attach file(s).

  5. #5
    Registered User
    Join Date
    01-02-2018
    Location
    VA
    MS-Off Ver
    Excel
    Posts
    8

    Re: Auto populate dates based on date range

    Here is the sample workbook.
    Attached Files Attached Files

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Auto populate dates based on date range

    Here you go. See attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-02-2018
    Location
    VA
    MS-Off Ver
    Excel
    Posts
    8

    Re: Auto populate dates based on date range

    For whatever reason it works great on the sample workbook but when I copy that formula into cell K2 on my working copy It does not work. I edited the formula to reflect the correct worksheet name of my working copy.

    I am using excel 2016
    Last edited by tmva; 01-03-2018 at 12:32 PM.

  8. #8
    Registered User
    Join Date
    01-02-2018
    Location
    VA
    MS-Off Ver
    Excel
    Posts
    8

    Re: Auto populate dates based on date range

    Here is what I have used for the formula:
    =IF('PM Data'!$H$12+ROW(A1)-1)<='PM Data'!$I$12,'PM Data'!$H$12+ROW(A1)-1),"")

    Which does not work even though it is the same as the sample workbook. What am I missing?

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Auto populate dates based on date range

    Well, it's pretty certain that the real workbook is NOT exactly the same as the sample workbook, otherwise there would not be a problem; so attach the real workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  10. #10
    Registered User
    Join Date
    01-02-2018
    Location
    VA
    MS-Off Ver
    Excel
    Posts
    8

    Re: Auto populate dates based on date range

    That is why I changed the formula to reflect PM Data which is the real workbook instead of Sample PM Data. Is there a better way of attaching it?

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Auto populate dates based on date range

    Better way of attaching it???

    Just attach the real workbook in the same way that you attached the sample one to post #5.

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Auto populate dates based on date range

    Hmm, only reason I can think of is that your cell contains text and not date value.

    If that's the case replace 'PM Data'!$I$12 in your formula with...
    'PM Data'!$I$12+0

+ 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. Auto Populate Calendar based on rental dates
    By NickolasH in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2017, 11:45 AM
  2. Auto-Populate date range in grade book sheets based from sheet 1 link
    By Marksman62 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-17-2016, 10:28 PM
  3. VBA auto return dates based on date range
    By Philly500 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2015, 12:36 PM
  4. [SOLVED] Creating a formula to populate range of cells between dates based on start and end dates
    By Rainmain82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-20-2014, 07:42 PM
  5. [SOLVED] Creating a formula to populate range of cells between dates based on start and end dates
    By Rainmain82 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2014, 07:55 AM
  6. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  7. Macro- Auto populate Dates in a range
    By abhishek_k in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2010, 12:44 PM

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