Determine Dates (Excel 97 SR2)

Topic

New Reply

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

Reply | Quote

Replies

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.

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

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:

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

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

Reply | Quote

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

Reply | Quote

(Edited by sdckapr on 27-Feb-03 15:05. Corrected a cell error)

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote