software

Hi,

I'm trying to get column B (dates) to be a copy of column A (same dates
but 1 month behind) and need to get the formula to ignore coloured cells
(weekends).. any ideas?--
lara5555
------------------------------------------------------------------------
lara5555's Profile: www.excelforum.com/member.php...foamp;userid=2741
View this thread: www.excelforum.com/showthread...hreadid=529519
List all weekend dates for the next 5 years in a lookup table, then test
dates for quot;is this a weekend date, if so ignore it otherwise use itquot;--
robert111
------------------------------------------------------------------------
robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=529519In B1, enter:
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,quot;quot;,DATE(YEAR(A1 ),MONTH(A1) 1,DAY(A1)))
then copy down

HTH
--
AP

quot;lara5555quot; gt; a écrit
dans le message de
...
gt;
gt; Hi,
gt;
gt; I'm trying to get column B (dates) to be a copy of column A (same dates
gt; but 1 month behind) and need to get the formula to ignore coloured cells
gt; (weekends).. any ideas?
gt;
gt;
gt; --
gt; lara5555
gt; ------------------------------------------------------------------------
gt; lara5555's Profile:
www.excelforum.com/member.php...foamp;userid=2741
gt; View this thread: www.excelforum.com/showthread...hreadid=529519
gt;
Friday is a weekend in France Ardus? I always knew you were civilized lt;bggt;

alternative

=IF(WEEKDAY(A1,2)gt;5,quot;quot;,DATE(YEAR(A1),MONTH(A1) 1,D AY(A1)))

Lara,

What happens when the calculated date is a weekend?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ardus Petusquot; gt; wrote in message
...
gt; In B1, enter:
gt; =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,quot;quot;,DATE(YEAR(A1 ),MONTH(A1) 1,DAY(A1)))
gt; then copy down
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;lara5555quot; gt; a écrit
gt; dans le message de
gt; ...
gt; gt;
gt; gt; Hi,
gt; gt;
gt; gt; I'm trying to get column B (dates) to be a copy of column A (same dates
gt; gt; but 1 month behind) and need to get the formula to ignore coloured cells
gt; gt; (weekends).. any ideas?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; lara5555
gt; gt; ------------------------------------------------------------------------
gt; gt; lara5555's Profile:
gt; www.excelforum.com/member.php...foamp;userid=2741
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=529519
gt; gt;
gt;
gt;
Week-ends stretch from Thursday night to Monday aftternoon!

quot;Bob Phillipsquot; gt; a écrit dans le message
de ...
gt; Friday is a weekend in France Ardus? I always knew you were civilized lt;bggt;
gt;
gt; alternative
gt;
gt; =IF(WEEKDAY(A1,2)gt;5,quot;quot;,DATE(YEAR(A1),MONTH(A1) 1,D AY(A1)))
gt;
gt; Lara,
gt;
gt; What happens when the calculated date is a weekend?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Ardus Petusquot; gt; wrote in message
gt; ...
gt; gt; In B1, enter:
gt; gt;
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,quot;quot;,DATE(YEAR(A1 ),MONTH(A1) 1,DAY(A1)))
gt; gt; then copy down
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;lara5555quot; gt; a
écrit
gt; gt; dans le message de
gt; gt; ...
gt; gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; I'm trying to get column B (dates) to be a copy of column A (same
dates
gt; gt; gt; but 1 month behind) and need to get the formula to ignore coloured
cells
gt; gt; gt; (weekends).. any ideas?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; lara5555
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; lara5555's Profile:
gt; gt; www.excelforum.com/member.php...foamp;userid=2741
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=529519
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;

Hi Bob,

Week started with a Monday and Tues to Sat have appeared as #NAME?Bob Phillips Wrote:
gt; Friday is a weekend in France Ardus? I always knew you were civilized
gt; lt;bggt;
gt;
gt; alternative
gt;
gt; =IF(WEEKDAY(A1,2)gt;5,quot;quot;,DATE(YEAR(A1),MONTH(A1) 1,D AY(A1)))
gt;
gt; Lara,
gt;
gt; What happens when the calculated date is a weekend?
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Ardus Petusquot; gt; wrote in message
gt; ...
gt; gt; In B1, enter:
gt; gt;
gt; =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,quot;quot;,DATE(YEAR(A1 ),MONTH(A1) 1,DAY(A1)))
gt; gt; then copy down
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;lara5555quot; gt; a
gt; écrit
gt; gt; dans le message de
gt; gt; ...
gt; gt; gt;
gt; gt; gt; Hi,
gt; gt; gt;
gt; gt; gt; I'm trying to get column B (dates) to be a copy of column A (same
gt; dates
gt; gt; gt; but 1 month behind) and need to get the formula to ignore coloured
gt; cells
gt; gt; gt; (weekends).. any ideas?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; lara5555
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; lara5555's Profile:
gt; gt; www.excelforum.com/member.php...foamp;userid=2741
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=529519
gt; gt; gt;
gt; gt;
gt; gt;--
lara5555
------------------------------------------------------------------------
lara5555's Profile: www.excelforum.com/member.php...foamp;userid=2741
View this thread: www.excelforum.com/showthread...hreadid=529519Don't you just love France (well I do)?

Bob

quot;Ardus Petusquot; gt; wrote in message
...
gt; Week-ends stretch from Thursday night to Monday aftternoon!
gt;
gt; quot;Bob Phillipsquot; gt; a écrit dans le message
gt; de ...
gt; gt; Friday is a weekend in France Ardus? I always knew you were civilized
lt;bggt;
gt; gt;
gt; gt; alternative
gt; gt;
gt; gt; =IF(WEEKDAY(A1,2)gt;5,quot;quot;,DATE(YEAR(A1),MONTH(A1) 1,D AY(A1)))
gt; gt;
gt; gt; Lara,
gt; gt;
gt; gt; What happens when the calculated date is a weekend?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Ardus Petusquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; In B1, enter:
gt; gt; gt;
gt; =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,quot;quot;,DATE(YEAR(A1 ),MONTH(A1) 1,DAY(A1)))
gt; gt; gt; then copy down
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;lara5555quot; gt; a
gt; écrit
gt; gt; gt; dans le message de
gt; gt; gt; ...
gt; gt; gt; gt;
gt; gt; gt; gt; Hi,
gt; gt; gt; gt;
gt; gt; gt; gt; I'm trying to get column B (dates) to be a copy of column A (same
gt; dates
gt; gt; gt; gt; but 1 month behind) and need to get the formula to ignore coloured
gt; cells
gt; gt; gt; gt; (weekends).. any ideas?
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; lara5555
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; lara5555's Profile:
gt; gt; gt; www.excelforum.com/member.php...foamp;userid=2741
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=529519
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
Really? What dates are you using, and are they real dates? I don't see that
behaviour in my spreadsheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;lara5555quot; gt; wrote in
message ...
gt;
gt; Hi Bob,
gt;
gt; Week started with a Monday and Tues to Sat have appeared as #NAME?
gt;
gt;
gt; Bob Phillips Wrote:
gt; gt; Friday is a weekend in France Ardus? I always knew you were civilized
gt; gt; lt;bggt;
gt; gt;
gt; gt; alternative
gt; gt;
gt; gt; =IF(WEEKDAY(A1,2)gt;5,quot;quot;,DATE(YEAR(A1),MONTH(A1) 1,D AY(A1)))
gt; gt;
gt; gt; Lara,
gt; gt;
gt; gt; What happens when the calculated date is a weekend?
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Ardus Petusquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; In B1, enter:
gt; gt; gt;
gt; gt;
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,quot;quot;,DATE(YEAR(A1 ),MONTH(A1) 1,DAY(A1)))
gt; gt; gt; then copy down
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; --
gt; gt; gt; AP
gt; gt; gt;
gt; gt; gt; quot;lara5555quot; gt; a
gt; gt; écrit
gt; gt; gt; dans le message de
gt; gt; gt; ...
gt; gt; gt; gt;
gt; gt; gt; gt; Hi,
gt; gt; gt; gt;
gt; gt; gt; gt; I'm trying to get column B (dates) to be a copy of column A (same
gt; gt; dates
gt; gt; gt; gt; but 1 month behind) and need to get the formula to ignore coloured
gt; gt; cells
gt; gt; gt; gt; (weekends).. any ideas?
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; lara5555
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; lara5555's Profile:
gt; gt; gt; www.excelforum.com/member.php...foamp;userid=2741
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=529519
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; lara5555
gt; ------------------------------------------------------------------------
gt; lara5555's Profile:
www.excelforum.com/member.php...foamp;userid=2741
gt; View this thread: www.excelforum.com/showthread...hreadid=529519
gt;