Assigning consecutive date ranges
 

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

write 7-27-06 in A1, in A2 to A30 write the formula =$A$1. In A31 write
the formula =A1+1. Copy the formula from A31 down as far as you like.

arno

Assigning consecutive date ranges
 

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

When I do the formula I get the error #value!?? Also what does INT mean? I
want to understand the formula also????

"Toppers" wrote:

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

I got it. Yeah! Now how does it recognize the next thirst records?? Or
will I have to post a new date after the previous 30????

"Toppers" wrote:

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

It simply adds 1 to the previous date every thirty records, so record 31 is
A1 +1, record 61 is A1+2 etc. I am assuming consective dates are required.

So starting with 27/07/2006 (UK date format) in A1, A1 to a30=27/07/2006,
then A31 (to A60) will be 28/07/2006, A61 (to A90) will be 29/07/2006 etc

HTH

"Brooke" wrote:

I got it. Yeah! Now how does it recognize the next thirst records?? Or
will I have to post a new date after the previous 30????

"Toppers" wrote:

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

I guess I'm wondering if there is a way Excel would automatically count the
30 records without me having to input it into each record. I still have to
copy the formula to each cell right???? Isn't there a way on the first cell
I can put the formula that will have the date magically appear in each cell
to A30 then A31 put in the next date to A61 and so forth.. I'm probably
asking alot hey. :)

"Toppers" wrote:

It simply adds 1 to the previous date every thirty records, so record 31 is
A1 +1, record 61 is A1+2 etc. I am assuming consective dates are required.

So starting with 27/07/2006 (UK date format) in A1, A1 to a30=27/07/2006,
then A31 (to A60) will be 28/07/2006, A61 (to A90) will be 29/07/2006 etc

HTH

"Brooke" wrote:

I got it. Yeah! Now how does it recognize the next thirst records?? Or
will I have to post a new date after the previous 30????

"Toppers" wrote:

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

Oh my gosh it just worked. I'm so sorry for the additional questions. I
must have had the wrong cell at first. I see where the =INT formula worked
all the way down.

Now is there a way to have it only do that to a certain persons name??
There is multiple sales reps within the worksheet and I want it to only sign
a date range to one particular rep??

Thank you so much.

"Toppers" wrote:

It simply adds 1 to the previous date every thirty records, so record 31 is
A1 +1, record 61 is A1+2 etc. I am assuming consective dates are required.

So starting with 27/07/2006 (UK date format) in A1, A1 to a30=27/07/2006,
then A31 (to A60) will be 28/07/2006, A61 (to A90) will be 29/07/2006 etc

HTH

"Brooke" wrote:

I got it. Yeah! Now how does it recognize the next thirst records?? Or
will I have to post a new date after the previous 30????

"Toppers" wrote:

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...

Assigning consecutive date ranges
 

Oops. One more question - Can iI add something to the formula to exclude
weekend dates???? Just M-F???????

"Brooke" wrote:

I guess I'm wondering if there is a way Excel would automatically count the
30 records without me having to input it into each record. I still have to
copy the formula to each cell right???? Isn't there a way on the first cell
I can put the formula that will have the date magically appear in each cell
to A30 then A31 put in the next date to A61 and so forth.. I'm probably
asking alot hey. :)

"Toppers" wrote:

It simply adds 1 to the previous date every thirty records, so record 31 is
A1 +1, record 61 is A1+2 etc. I am assuming consective dates are required.

So starting with 27/07/2006 (UK date format) in A1, A1 to a30=27/07/2006,
then A31 (to A60) will be 28/07/2006, A61 (to A90) will be 29/07/2006 etc

HTH

"Brooke" wrote:

I got it. Yeah! Now how does it recognize the next thirst records?? Or
will I have to post a new date after the previous 30????

"Toppers" wrote:

Put your starting date in A1 and this in A2 and copy down:

=INT($A$1+INT(ROW()-1)/30)

HTH

"Brooke" wrote:

I would like to have Excel assign a date to a specific line of records. I
would like it to assign a date to 30 records then assign the next date to the
next 30 records. Can this be done???

Example -
7-27-06 - 30 records should have this date
7-28-06 - The next 30 records would have this date
and so forth until all records have a date assigned to them.

Is there a formula I can write that would accomplish the above without
having to count each 30 records put the date then count the next 30 records,
etc...