| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
| Counting Cells with certain date ranges as values | Excel Worksheet Functions | |||
| macro to change date ranges | Excel Discussion (Misc queries) | |||
| search for latest date | Excel Worksheet Functions | |||
| count date ranges | Excel Worksheet Functions | |||
Linear Mode
