Date
 

I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance

Hi David

there is a weekday function which tells you what day of the week an event is
(by default it puts Sunday as 1) so you could do something like this:

=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"David" wrote in message
...
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell
to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance

I think it could be better in this way:

=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi David

there is a weekday function which tells you what day of the week an event
is (by default it puts Sunday as 1) so you could do something like this:

=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"David" wrote in message
...
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell
to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance

Hi Franz

why better?

--
Cheers
JulieD

"Franz" wrote in message
...
I think it could be better in this way:

=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi David

there is a weekday function which tells you what day of the week an event
is (by default it puts Sunday as 1) so you could do something like this:

=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"David" wrote in message
...
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is
a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell
to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance

Ooops...
sorry...

This should work

=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)+2,IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,DATE(YEAR(F3),M ONTH(F3)+1,DAY(F3)+1,DATE(YEAR(F3),MONTH(F3)+1,DAY (F3)))



"Franz" ha scritto nel messaggio
...
I think it could be better in this way:

=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi David

there is a weekday function which tells you what day of the week an event
is (by default it puts Sunday as 1) so you could do something like this:

=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"David" wrote in message
...
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is
a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell
to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance

On Mon, 18 Apr 2005 07:24:09 -0700, "David"
wrote:

I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance


I would use a somewhat different approach.

I have assumed that your "index date" is in F3. And that your formula for
subsequent dates are in F4..Fn.

I have also assumed, which may not be the case, that if your index date is, let
us say, 31 Jan, that when you add one month you would like the result to be 28
(or 29) Feb, unless that date is a weekend in which case you would accept a
March date.

If those assumptions are correct, then I would use the formula:

=WorKDAY(edate($F$3,ROW()-3)-1,1)

If the WORKDAY or EDATE functions are not available, and return the #NAME?
error, install and load the Analysis ToolPak add-in.

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.

If those assumptions are not correct, please post back.



--ron

That is exactly right, but I have no idea how the formula is working, because
of $F$3 anchors.

"Ron Rosenfeld" wrote:

On Mon, 18 Apr 2005 07:24:09 -0700, "David"
wrote:

I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that cell to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance


I would use a somewhat different approach.

I have assumed that your "index date" is in F3. And that your formula for
subsequent dates are in F4..Fn.

I have also assumed, which may not be the case, that if your index date is, let
us say, 31 Jan, that when you add one month you would like the result to be 28
(or 29) Feb, unless that date is a weekend in which case you would accept a
March date.

If those assumptions are correct, then I would use the formula:

=WorKDAY(edate($F$3,ROW()-3)-1,1)

If the WORKDAY or EDATE functions are not available, and return the #NAME?
error, install and load the Analysis ToolPak add-in.

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.

If those assumptions are not correct, please post back.



--ron

Hi Julie

I'm sorry...
Sometimes I should read better...
Your formula was very good

Hope you will forgive me

Also for my English...

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi Franz

why better?

--
Cheers
JulieD

"Franz" wrote in message
...
I think it could be better in this way:

=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi David

there is a weekday function which tells you what day of the week an
event is (by default it puts Sunday as 1) so you could do something like
this:

=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"David" wrote in message
...
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date is
a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that
cell to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance

On Mon, 18 Apr 2005 12:28:01 -0700, "David G" <David
wrote:

That is exactly right, but I have no idea how the formula is working, because
of $F$3 anchors.

=WorKDAY(edate($F$3,ROW()-3)-1,1)

Let's start from the inside:

ROW() returns the row in which the formula is located.

So in F4, ROW() will return a 4; in F5 ROW() will return a 5 and so forth.

Since the base cell is in F3, we subtract 3 from this to determine how many
months to add to the value in F3.

EDATE(start_date,months) adds this previously determined number of months to
the date in F3. It takes care of adjusting for the end of the month so that
one month after Jan 31 will still be the end of February.

The WORKDAY function is then used to eliminate weekends. By subtracting 1 from
the date we compute with EDATE, and then adding 1 WORKDAY to it, we will
effectively skip over the weekends.

Hope this is clear.


--ron

Hi Franz

no apology necessary ....

--
Cheers
JulieD

"Franz" wrote in message
...
Hi Julie

I'm sorry...
Sometimes I should read better...
Your formula was very good

Hope you will forgive me

Also for my English...

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi Franz

why better?

--
Cheers
JulieD

"Franz" wrote in message
...
I think it could be better in this way:

=IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7, 2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1 ,1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))

Bye

Franz


"JulieD" ha scritto nel messaggio
...
Hi David

there is a weekday function which tells you what day of the week an
event is (by default it puts Sunday as 1) so you could do something
like this:

=DATE(YEAR(F3),MONTH(F3)+1,DAY(F3))+IF(WEEKDAY(DAT E(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DA TE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"David" wrote in message
...
I have a series of dates in column f, and the formula is
DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)), but I need to check if this date
is a
weekend or weekday. Let us say 06/18/05 is a saturday, I need that
cell to
display 06/20/05, is that possible. Any help you can provide would be
greatly appreciated.

Thanks in advance