Date

D

David

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
 
J

JulieD

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(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=7,2,IF(WEEKDAY(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,1,0))
 
F

Franz

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
 
J

JulieD

Hi Franz

why better?

--
Cheers
JulieD

Franz said:
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
Click to expand...
 
F

Franz

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(DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))=1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)+1,DATE(YEAR(F3),MONTH(F3)+1,DAY(F3)))
 
R

Ron Rosenfeld

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
Click to expand...

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
 
D

David G

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

Franz

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
 
R

Ron Rosenfeld

On Mon, 18 Apr 2005 12:28:01 -0700, "David G" <David
That is exactly right, but I have no idea how the formula is working, because
of $F$3 anchors.
Click to expand...

=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
 
Top