I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be 12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

With
A1: (a date)

This formula return the preceeding weekday:
A2: =A1-CHOOSE(MIN(WEEKDAY(A1),3),2,3,1)

or...if you have the Analysis ToolPak installed:
A2: =WORKDAY(A1,-1)

Either way, copy the formula down as far as you need.
(remember to format the results as dates.)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Nigel" wrote in message
...
I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be
12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

If weekends are the only consideration:
In A2:
=WORKDAY(A1,-1)
In A3:
=WORKDAY(A1,-2)
etc.

To also exclude holidays, create a named range listing upcoming holidays.
Use the named range as the third argument in your formula:
=WORKDAY(A1,-1,Holidays)


"Nigel" wrote:

I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be 12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

Another way:
Put 12/18/2007 in A1
Put 12/19/2007 in A2

Select A1:A2
right click on the autofill button on the bottom right corner of the selection
and drag down as far as you need.

When you let go of the rightmouse button, you'll be prompted with a bunch of
options. You can choose Fill Weekdays.



Nigel wrote:

I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be 12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

--

Dave Peterson

A2 =Today()
Select A2 and as many cells as you want
Then
Edit|Fill|Series
Select weekday and step value of -1

Mike

"Nigel" wrote:

I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be 12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

Almost forgot, this function is part of the Analysis ToolPak. If it gives
you an error, go to Tools, Add-Ins, and check Analysis ToolPak to make it
available.

"BoniM" wrote:

If weekends are the only consideration:
In A2:
=WORKDAY(A1,-1)
In A3:
=WORKDAY(A1,-2)
etc.

To also exclude holidays, create a named range listing upcoming holidays.
Use the named range as the third argument in your formula:
=WORKDAY(A1,-1,Holidays)


"Nigel" wrote:

I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be 12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

Tue, 18 Dec 2007 06:48:02 -0800 from Nigel
:
I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14,

WEEKDAY(...) returns 1 to 7 for Sunday through Saturday. So you want
A1-1 usually, but A1-3 if weekday(A1-1) is a 7 or 1:

=IF(OR(WEEKDAY(A1-1)=7,WEEKDAY(A1-1)=1),A1-3,A1-1)

I'm not too fond of computing WEEKDAY twice, but there's a better way
using MOD. MOD(1,7) is 1 and MOD(7,7) is 0, so a faster way to test
for 1 or 7 is

=IF(MOD(WEEKDAY(A1-1),7)<2,A1-3,A1-1)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

If you use WEEKDAY(A1-1,2) or WEEKDAY(A1-1,3), the numbers for Saturday and
Sunday will be adjacent, but using WORKDAY is probably the neater solution.
--
David Biddulph

"Stan Brown" wrote in message
t...

WEEKDAY(...) returns 1 to 7 for Sunday through Saturday. So you want
A1-1 usually, but A1-3 if weekday(A1-1) is a 7 or 1:

=IF(OR(WEEKDAY(A1-1)=7,WEEKDAY(A1-1)=1),A1-3,A1-1)

I'm not too fond of computing WEEKDAY twice, but there's a better way
using MOD. MOD(1,7) is 1 and MOD(7,7) is 0, so a faster way to test
for 1 or 7 is

=IF(MOD(WEEKDAY(A1-1),7)<2,A1-3,A1-1)

Tue, 18 Dec 2007 06:48:02 -0800 from Nigel
:
I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14,

I didn't notice that you were going back in time.

Put 12/18/2007 in A1
put 12/17/2007 in A2
and then do the rest of those instructions.

Dave Peterson wrote:

Another way:
Put 12/18/2007 in A1
Put 12/19/2007 in A2

Select A1:A2
right click on the autofill button on the bottom right corner of the selection
and drag down as far as you need.

When you let go of the rightmouse button, you'll be prompted with a bunch of
options. You can choose Fill Weekdays.

Nigel wrote:

I am trying to skip weekends from a date in a spreadsheet,

In Cell A1 I have todays date

In cell A2 A3 A4 A5 I need it to be the previous days date so based on
todays date
A2 would be 12-17, A3 tho would have to be 12-14, A4 would have to be 12-13
and so on

tomorrow tho

A2 would be 12-18 A3 would be 12-17 A4 should be 12-14 ...

what is the easiest way to accomplish this

thanks in advance

--

Dave Peterson

--

Dave Peterson

Tue, 18 Dec 2007 15:55:23 -0000 from <"David Biddulph" <groups [at]
biddulph.org.uk:
If you use WEEKDAY(A1-1,2) or WEEKDAY(A1-1,3), the numbers for Saturday and
Sunday will be adjacent, but using WORKDAY is probably the neater solution.

I agree with using WORKDAY.

The only reason I didn't suggest it is that I didn't know about it.
Searching for "weekday" in Excel 2003's Insert | Function does bring
up NETWORKDAYS but not WORKDAY. (WORKDAY is in the help file, just
not presented by Insert | Function.)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Interesting! With my Excel 2003, the See Also list from WEEKDAY in Insert/
Function gets me to a "Date and Time Functions" list which shows me WORKDAY
as well as NETWORKDAYS. Does your list show the YEAR and YEARFRAC
functions (which are the ones that follow WORKDAY in my list)? I wonder
whether there is something wrong with the scrolling in your list?
--
David Biddulph

"Stan Brown" wrote in message
t...
Tue, 18 Dec 2007 15:55:23 -0000 from <"David Biddulph" <groups [at]
biddulph.org.uk:
If you use WEEKDAY(A1-1,2) or WEEKDAY(A1-1,3), the numbers for Saturday
and
Sunday will be adjacent, but using WORKDAY is probably the neater
solution.

I agree with using WORKDAY.

The only reason I didn't suggest it is that I didn't know about it.
Searching for "weekday" in Excel 2003's Insert | Function does bring
up NETWORKDAYS but not WORKDAY. (WORKDAY is in the help file, just
not presented by Insert | Function.)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Wed, 19 Dec 2007 06:23:53 -0000 from <"David Biddulph" <groups [at]
biddulph.org.uk:
Interesting! With my Excel 2003, the See Also list from WEEKDAY in Insert/
Function gets me to a "Date and Time Functions" list which shows me WORKDAY
as well as NETWORKDAYS. Does your list show the YEAR and YEARFRAC
functions (which are the ones that follow WORKDAY in my list)? I wonder
whether there is something wrong with the scrolling in your list?

Insert | Function
Type weekday in search box and hit Go
Three hits: WEEKDAY, NETWORKDAYS, WEEKNUM

in Excel 2003 SP1

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/