Date Formula
 

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.

Date Formula
 

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


--
Biff
Microsoft Excel MVP


"Brooke" wrote in message
...
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.

Date Formula
 

Try this:

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


"Brooke" wrote:

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.

Date Formula
 

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


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Brooke" wrote in message
...
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.

Date Formula
 

Copy down as far as needed

"Teethless mama" wrote:

Try this:

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


"Brooke" wrote:

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.

Date Formula
 

On Tue, 26 Feb 2008 12:58:01 -0800, Brooke
wrote:

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

Date Formula
 

Not sure what you're wanting but...

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Your formula is including Sat, and Sun. OP doesn't want to include Sat,
and
Sun based on his/her formula


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"Brooke" wrote in message
...
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.

Date Formula
 

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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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


--
Biff
Microsoft Excel MVP


"Brooke" wrote in message
...
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.

Date Formula
 

On Tue, 26 Feb 2008 23:24:50 -0500, "T. Valko" wrote:

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)


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

Date Formula
 

In my original reply I noted:

Not sure what you're wanting but...


--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Tue, 26 Feb 2008 23:24:50 -0500, "T. Valko"
wrote:

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)


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

Date Formula
 

On Wed, 27 Feb 2008 12:45:23 -0500, "T. Valko" wrote:

In my original reply I noted:

Not sure what you're wanting but...


--
Biff
Microsoft Excel MVP

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