Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine Dates (Excel 97 SR2)

    I have a user with an interesting request. They get random sample picks of bus routes driven. The data indicates the day of the week.
    The week starts on Saturday and ends on Friday.
    There will be over 80 rows of data.
    Each four (4) rows is within a 1 week period. The next four rows is the following week, etc.
    The data may not be in date order (random generated).
    User wants to be able to type the date for the first row of data and have the worksheet generate the rest of the dates.
    A sample workbook is attached.
    Any help will be appreciated.

    Thanks,
    Chuck
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Dates (Excel 97 SR2)

    If you will put this formula in cell M3 and then fill it down for as many dates as you want, I think it will generate what you want as long as you put a Saturday date in cell L2.

    <pre>=$L$2+INT((ROW()-3)/4)*7+INT(RANDBETWEEN(0,6))
    </pre>


    You may wat to put this formula in row 2 so that you have a random date for all rows.

    Unfortunately, this formula is going to recalculate and change the dates every time the worksheet recalculates. If this is going to create a problem (I think that it will), then there are a number of things that you can do.

    1- Use this formula on another sheet or in some unseen columns on this sheet, and then when you want new dates, change the starting date, copy the new dates and use Paste/Special/Values to paste just the date values back where you want them.

    2- Use a VBA macro to generate the dates. If you want to do this, it would look something like this:

    <pre>Public Sub GenDates()
    Dim datStart As Date, I As Long
    datStart = Worksheets("samplepick").Range("L2")
    Randomize
    For I = 0 To 79
    Worksheets("samplepick").Range("M2").Offset(I, 0).Value = datStart + Int(I / 4) * 7 + Int(7 * Rnd)
    Next I
    End Sub
    </pre>


    That routine will will generate 80 dates starting in cell M2 based on the week beginning date in L2.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Dates (Excel 97 SR2)

    Legare,

    Sorry for not being more clear. The worksheet the user recieves is make up of random picks. Each row was randomly selected to be in the samplepick.
    The user wants to know what date should be associated with each day.
    The sample I sent shows the first row date (entered by user) as 3/16/2003. The next three rows are all in the same week. So the Thursday of the same week has to be 3/20/2003.
    He is NOT looking for a random number generator -That part was done by his samplepick software. Hope this helps clarify.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Dates (Excel 97 SR2)

    Steve,
    I tried copying your formula into M2 and copied it down.
    Only one date matched. I am attaching the workbook for you to look at.
    I am sure this is the right track, but I got a detour!

    Thanks,
    Chuck
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Determine Dates (Excel 97 SR2)

    <P ID="edit" class=small>(Edited by sdckapr on 27-Feb-03 15:05. Corrected a cell error)</P>Try this formula in M3 and copy it down. (one line). The spacing in the strings is critical. You could also use lookup to get day of week (Each day is padded with spaces at the end to make it as long as Wednesday)

    =+$L$2-(FIND($H$2,"Saturday Sunday Monday Tuesday WednesdayThursday Friday")+8)/9+(FIND($H3,"Saturday Sunday Monday Tuesday WednesdayThursday Friday")+8)/9+7*INT((ROW()-ROW($L$2))/4)

    It uses the start date, the day of week from Col H, and the row # to get the week.

    It matches what you listed in your example.

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Determine Dates (Excel 97 SR2)

    Here I corrected this.

    1) the formula was to start in M3 NOT M2 (my mistake, I edited my original post to correct)
    2) The "dayofWeek" string got changed, you lost some spaces, this seems to be a Lounge problem(?). When it got copied it seems to have gotten "TRIM"med.

    Steve
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Dates (Excel 97 SR2)

    Steve,

    Dead on! Works great! It even works when I copy it to column L.
    This is what the user said he wanted and he will be very happy.

    I thank you and the entire Forum in general.
    I Love this place!

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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