Assigning consecutive dates

[Guest]

I received the answer on assigning consecutive dates but what can be done to
the formula

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

TO:

1. Not include weekends
2. Only assign the date to a certain person's name if there is multiple reps
on the spreadsheet.


Thank you so much.
Brooke Medvecky

 

[Bob Phillips]

I received the answer on assigning consecutive dates but what can be done to
the formula

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

TO:

1. Not include weekends

Don't get your formula.

My suggestion

=A1+IF(WEEKDAY(A1)=6,3,1)

2. Only assign the date to a certain person's name if there is multiple reps
on the spreadsheet.

Don't understand that bit.

 

[Guest]

Thank you for the formula. I will example. I have multiple sales people
within a spreadsheet. I want to only assign a date to one particular
individual within the spreadsheet without having to copy it into another
worksheet. Then, I want to assign the same date to 30 records. After those
30 assign the next day and so forth. I need to exclude weekends when
assigning dates.

Does this help? Please advise.
thanks
Brooke

 

[Bob Phillips]

Okay, I think I get it.

Try this

=INT($A$1+INT(ROW()-1)/30)+(WEEKDAY(INT($A$1+INT(ROW()-1)/30))=7)*2

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

When I write this formula and use the start date 8-14-06 it is computing the
next date as 08-12-06. This would be the weekend date???

I just used your formula =A1+IF(weekday(A1)=6,3,1 and just then added /30.
So now it reads =A1+IF(WEEKDAY(A1)=6,3,1/30 - This excluded the weekend days
and then put a new date after every 30 records. Yeah.

Now can you tell me what 6,3,1 is all about? I want to understand the
formula and I'm not sure why you used those numbers.
Thanks so much for the quick responses.

 

[Bob Phillips]

You have lost me. If you start with 14th Aug 2006, the next date is 15th, at
least it is in my tests. I fail to see how it could possibly count
backwards.

In my original formula, IF(WEEKDAY(A1)+6 just tests if the previous date is
a Friday, if some return 3 else return 1. The result of this test is added
to A1 to skip weekend dates.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I got it. I forgot a parenthesis so it was adding 7 days to the first date
inputted. Weird.

I appreciate the help.

 

[Guest]

Bob,
Try copying the formula down about 500 records. For some reason it starts
the date range over. So I have it started at 9-5-06 and then after it hits
09-11-06 it starts back to 9-10-06 and puts 09-11-06 again. Not sure why so
it reads 09-05-, 09-06, 09-07, 09-08, based on 30 records then goes to
09-11-06 for the next 30 records, but when it goes to the next 30 records it
goes to 09-10-06????? Then it repeats 09-11-06 again then starts to 09-12-06.


Let me know your thoughts. I have 1500 records. Is it a copying issue??

 

[Bob Phillips]

Uuum, tricky eh.

Here is an alternative approach, which seems to work correctly even in those
situations.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

So what is that alternative approach? Did I loose you know??

 

[Bob Phillips]

Well if you must have the solution as well as my eloquence <G>

=A1+IF(COUNTIF($A$1:A1,A1)=30,IF(WEEKDAY(A1)=6,3,1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Well it seems all of these solutions work for the 1st 150 records, but they
all still repeat after the 1st 5 days. Why is that?? It goes from 09-05-06
to 09-08-06 then even includes the first Monday correctly. After that it
factors in the 10th for the next 30 records then starts the 11th to the 15th
includes the first Monday correctly. After that week it factors in the 17th
for the next 30 records etc.... Why is it including the Sunday date once the
first week is computed??

Is it a formula sequence issue? I apologize for the questions, but this one
is out of my league.

 

[Bob Phillips]

No it doesn't, just checked again, and I get this sequence of dates

05-Sep
06-Sep
07-Sep
08-Sep
11-Sep
12-Sep
13-Sep
14-Sep
15-Sep
18-Sep
19-Sep
20-Sep
21-Sep
22-Sep
25-Sep
26-Sep
27-Sep
28-Sep
29-Sep



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks again Bob. I will maybe try reformatting the date range to see if
this is my problem. Not sure why I'm getting that error. I appreciate your
time and patience with me.

 

[Bob Phillips]

I've posted an example at http://cjoint.com/?ioxTMVQEAO

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)