Date Formula

[Brooke]

I have the following formula I created and was wondering instead of it
completing each consecutive weekday is there a way to have it take a date
5-5-08 and use that same day every 5 lines then create the next weekday add
that to 5 lines and continue down 1700 records???

=IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1)) - Where cell K2 is the
start date.

I have a 1700 record spreadsheet that instead of keying a date myself every
5 records having a formula do it for me.

Please Help.

 

[T. Valko]

Not sure what you're wanting but...

A1 = 5/5/2008 (m/d/y)

Enter this formula in A2:

=DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1))

When copied down, the formula will repeat each date 5 times then increment
by 1 day:

A1 = 5/5/2008
A2 = 5/5/2008
A3 = 5/5/2008
A4 = 5/5/2008
A5 = 5/5/2008
A6 = 5/6/2008
A7 = 5/6/2008
A8 = 5/6/2008
A9 = 5/6/2008
A10 = 5/6/2008
A11 = 5/7/2008
etc

 

[Teethless mama]

Try this:

K2: 5/5/2008
K3: =IF(COUNTIF(K$2:K2,K2)<5,K2,WORKDAY(K2,1))

 

[Teethless mama]

Your formula is including Sat, and Sun. OP doesn't want to include Sat, and
Sun based on his/her formula

 

[Teethless mama]

Copy down as far as needed

Try this:

K2: 5/5/2008
K3: =IF(COUNTIF(K$2:K2,K2)<5,K2,WORKDAY(K2,1))

 

[Ron Rosenfeld]

I have the following formula I created and was wondering instead of it
completing each consecutive weekday is there a way to have it take a date
5-5-08 and use that same day every 5 lines then create the next weekday add
that to 5 lines and continue down 1700 records???

=IF(WEEKDAY(K2)=7,K2+2,IF(WEEKDAY(K2)=6,K2+3,K2+1)) - Where cell K2 is the
start date.

I have a 1700 record spreadsheet that instead of keying a date myself every
5 records having a formula do it for me.

Please Help.

You can try this formula:

With your starting date in A1:

A2: =WORKDAY($A$1,INT(ROWS($5:5)/5))

Fill down as far as required.

Change $A$1 to refer to the cell reference of your starting date.
==========================================
If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
================================
--ron

 

[T. Valko]

Not sure what you're wanting but...

 

[T. Valko]

Improvement:

A1 = 5/5/2008 (m/d/y)
Enter this formula in A2:
=DATE(YEAR(A$1),MONTH(A$1),DAY(A$1-1)+CEILING(ROWS(A$1:A2)/5,1))

The formula can be reduced to:

=A$1-1+CEILING(ROWS(A$1:A2)/5,1)

 

[Ron Rosenfeld]

Improvement:


The formula can be reduced to:

=A$1-1+CEILING(ROWS(A$1:A2)/5,1)

The OP requested to return only Weekdays (as did the OP's formula). Your
suggestions also return Saturdays and Sundays.
--ron

 

[T. Valko]

In my original reply I noted:

Not sure what you're wanting but...

 

[Ron Rosenfeld]

In my original reply I noted:

Well, since he specifically requested WEEKdays, and since the formula he was
using excluded Saturdays and Sundays, I guess I didn't have the same
uncertainty on this point as you.
--ron