LinkBack URL
About LinkBacks
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?
Formula with dates
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?
List all weekend dates for the next 5 years in a lookup table, then test dates for "is this a weekend date, if so ignore it otherwise use it"
In B1, enter:
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
then copy down
HTH
--
AP
"lara5555" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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:
http://www.excelforum.com/member.php...fo&userid=2741
> View this thread: http://www.excelforum.com/showthread...hreadid=529519
>
Friday is a weekend in France Ardus? I always knew you were civilized <bg>
alternative
=IF(WEEKDAY(A1,2)>5,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
Lara,
What happens when the calculated date is a weekend?
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> In B1, enter:
> =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
> then copy down
>
> HTH
> --
> AP
>
> "lara5555" <[email protected]> a écrit
> dans le message de
> news:[email protected]...
> >
> > 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:
> http://www.excelforum.com/member.php...fo&userid=2741
> > View this thread:
http://www.excelforum.com/showthread...hreadid=529519
> >
>
>
Week-ends stretch from Thursday night to Monday aftternoon!
"Bob Phillips" <[email protected]> a écrit dans le message
de news:%[email protected]...
> Friday is a weekend in France Ardus? I always knew you were civilized <bg>
>
> alternative
>
> =IF(WEEKDAY(A1,2)>5,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
>
> Lara,
>
> What happens when the calculated date is a weekend?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ardus Petus" <[email protected]> wrote in message
> news:[email protected]...
> > In B1, enter:
> >
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
> > then copy down
> >
> > HTH
> > --
> > AP
> >
> > "lara5555" <[email protected]> a
écrit
> > dans le message de
> > news:[email protected]...
> > >
> > > 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:
> > http://www.excelforum.com/member.php...fo&userid=2741
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=529519
> > >
> >
> >
>
>
Hi Bob,
Week started with a Monday and Tues to Sat have appeared as #NAME?
Originally Posted by Bob Phillips
Don't you just love France (well I do)?
Bob
"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> Week-ends stretch from Thursday night to Monday aftternoon!
>
> "Bob Phillips" <[email protected]> a écrit dans le message
> de news:%[email protected]...
> > Friday is a weekend in France Ardus? I always knew you were civilized
<bg>
> >
> > alternative
> >
> > =IF(WEEKDAY(A1,2)>5,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
> >
> > Lara,
> >
> > What happens when the calculated date is a weekend?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:[email protected]...
> > > In B1, enter:
> > >
> =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
> > > then copy down
> > >
> > > HTH
> > > --
> > > AP
> > >
> > > "lara5555" <[email protected]> a
> écrit
> > > dans le message de
> > > news:[email protected]...
> > > >
> > > > 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:
> > > http://www.excelforum.com/member.php...fo&userid=2741
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=529519
> > > >
> > >
> > >
> >
> >
>
>
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)
"lara5555" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Bob,
>
> Week started with a Monday and Tues to Sat have appeared as #NAME?
>
>
> Bob Phillips Wrote:
> > Friday is a weekend in France Ardus? I always knew you were civilized
> > <bg>
> >
> > alternative
> >
> > =IF(WEEKDAY(A1,2)>5,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
> >
> > Lara,
> >
> > What happens when the calculated date is a weekend?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Ardus Petus" <[email protected]> wrote in message
> > news:[email protected]...
> > > In B1, enter:
> > >
> >
=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=6,"",DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
> > > then copy down
> > >
> > > HTH
> > > --
> > > AP
> > >
> > > "lara5555" <[email protected]> a
> > écrit
> > > dans le message de
> > > news:[email protected]...
> > > >
> > > > 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:
> > > http://www.excelforum.com/member.php...fo&userid=2741
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=529519
> > > >
> > >
> > >
>
>
> --
> lara5555
> ------------------------------------------------------------------------
> lara5555's Profile:
http://www.excelforum.com/member.php...fo&userid=2741
> View this thread: http://www.excelforum.com/showthread...hreadid=529519
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks