Skip Weekends in date

[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, 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

 

[Ron Coderre]

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)

 

[BoniM]

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)

 

[Dave Peterson]

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.

 

[Mike H]

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

Mike

 

[BoniM]

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.

 

[Stan Brown]

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)

 

[David Biddulph]

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.

 

[Dave Peterson]

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.

 

[Stan Brown]

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.)

 

[David Biddulph]

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

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/

 

[Stan Brown]

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