+ Reply to Thread
Results 1 to 6 of 6

Populating column with dates based on start and end date

  1. #1
    Registered User
    Join Date
    07-13-2011
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    21

    Populating column with dates based on start and end date

    Hi there

    I was wondering if someone could help.

    I have a spreadsheet were the user will input solar radiation data. This data will have been collected on a particular date, but I don’t want the user to input the date manually , what I would like to do is for the user to input a start date and end date for the data and for excel to populate the date column based on this information.

    For example if I have a months worth of hourly data from April to be entered into my spreadsheet. I want the user to enter in the start date of 1/4/2011 and the end date of 30/4/2011. And for excel to put 1/4/2011 in the first 24 cells, and then the 2/4/2011 in the next 24 cells and so on.

    Does anybody know how I could achieve this / if it is even possible?

    I don’t mind using VBA if this is the only way it can be achieved.

    Thank you in advance

    David
    Last edited by mdavid800; 08-07-2011 at 12:49 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Populating column with dates based on start and end date

    here you go enter start in a1 end in b1
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    07-13-2011
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    21

    Re: Populating column with dates based on start and end date

    Thank you very much works like a charm.

  4. #4
    Registered User
    Join Date
    07-13-2011
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    21

    Re: Populating column with dates based on start and end date

    Martin

    if i wanted to do a similar thing but instead of putting the date into 24 cells for every hour i wanted to put the dates into 1440 cells for every minute would it be possible to modify the equation to do so.

    Cheers David

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Populating column with dates based on start and end date

    dont see why not
    =IF(ROW(A1)>(1+$B$1-$A$1)*1440,"",$A$1+FLOOR(ROW(A1440)/1440,1)-1)

  6. #6
    Registered User
    Join Date
    07-13-2011
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    21

    Re: Populating column with dates based on start and end date

    Ah i was missing one of the cell references

    Thanks for all the help

    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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