1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Who can resolve this excel problem?

Discussion in 'BlackHat Lounge' started by [RusTy], Sep 10, 2009.

  1. [RusTy]

    [RusTy] Junior Member

    Joined:
    May 10, 2009
    Messages:
    112
    Likes Received:
    2
    I am making an excel file with spreadsheet for each day of the week.

    I would like to make a cell on top which I can choose Sunday days from
    a period of 15 days back and 15 days after the date I am writing the file.

    Example of drop-list menu which will be showed for Sunday of today:
    Sunday Aug-31
    Sunday Sep-7
    Sunday Sep-14
    Sunday Sep-21

    Anyone can help me on this one? Thanks a lot!
    :)
     
  2. [RusTy]

    [RusTy] Junior Member

    Joined:
    May 10, 2009
    Messages:
    112
    Likes Received:
    2
    $2 if problem solved.
     
  3. CasinoJack

    CasinoJack BANNED BANNED

    Joined:
    Jan 17, 2009
    Messages:
    711
    Likes Received:
    1,418
    Yes it is. Each day of the week has a number associated with it, Sunday is day
    1, Monday is day 2 and so on through to Saturday which is day 7. You can extract this from a date in Excel using the Weekday function. So, if your start date is a Monday you'll just add 21 days to it to get the result you want. If it is a Tuesday you'll add 21 days but since this gives you a Tuesday you need to add another 6 days to get the result you want. If your start date is a Wednesday you'll add 21 days plus an extra 5 to get where you need to be. If your start date is a Thursday you'll add 21 + 4 days, for Friday add 21 +3 days, for Saturday 21 +2 days and for Sunday 21 + 1 days. A Choose function can help you link the day of the week to the right number of extra days to add. Here's the formula to use, assuming the start date is in cell A2:

    =A2+21+Choose(weekday(A2),1,0,6,5,4,3,2,)

    PS I copied this from somewhere then my ie crashed so I can show you source...
     
  4. [RusTy]

    [RusTy] Junior Member

    Joined:
    May 10, 2009
    Messages:
    112
    Likes Received:
    2
    Thank you for the try. But I need drop-down list and not to write it every time.
     
  5. 195471

    195471 Regular Member

    Joined:
    Oct 11, 2008
    Messages:
    417
    Likes Received:
    260
    If I understand you correctly, then you should be able to do this with the steps below. I used Excel 2007, so if you've got some other version, the steps might be different.

    1) For simplicity, start with one workbook that has two worksheets - rename the first to "dropdown" and the second to "daterange". The first worksheet is where you'll put the drop down menu, and the second is where you'll pull the valid dates from.

    2) Go to the daterange worksheet and enter the following in the specified cells:

    A1 --> =TODAY()-14
    A2 --> =TODAY()-7
    A3 --> =TODAY()
    A4 --> =TODAY()+7
    A5 --> =TODAY()+14

    Select all 5 cells, and give them a name (I chose "validdates") by entering the name in the name bar. Once you have entered the name, press ENTER.

    [​IMG]

    3) Now go to the dropdown worksheet and insert a drop down list in A1. To do that, go to the Data tab and select the Data Validation icon in the ribbon. Select the first option, which will bring up a dialog box. Under "Allow", select "List", and enter "=validdates" (without quotes) in the Source box. Your drop down should pull the date values from the other worksheet.

    [​IMG]

    The dates that I used were for illustration purposes. You'll need to monkey around with them to get exactly what you want for each day of the week. The point here is to set up the date formulas in one worksheet so that they can be used in any other worksheet.

    I hope the above helps in some way. :)
     
    • Thanks Thanks x 1
  6. [RusTy]

    [RusTy] Junior Member

    Joined:
    May 10, 2009
    Messages:
    112
    Likes Received:
    2
    Wow! I couldn't asked for a better answer!
    Thanks a lot!!