IF Statement

T

Tech Support

Hi people quick quest been a long time since i done this kind of thing

How would i program a excel docuement to say if it is the 1st then ad 2 days
and so on to go through the week but if it is a friday then i need to add 4
to miss out the weekend. really it would be good if the if statement would
work in MS Word.
 
S

Socko

First you may find the weekday as below, assuming cell a1 has a
reference...

=weekday(a1)

Then passing on if function, to find if weekday=5(i.e., Friday), if
yes then, + 4

=IF(WEEKDAY(A1)=5,A1+4,A1+2)

Finally, to format it back as date, you may use text function...

The final formula as follows

=TEXT(IF(WEEKDAY(A1)=5,A1+4,A1+2),"mm/dd/yyyy")

I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, and other resources
Please visit: http://socko.wordpress.com
 
J

JLGWhiz

Here is a snippet that looks at the current date and determines the next work
day. If the current day is a Friday it will give Monday's date as the next
work day. Maybe you can use it to develop what you need.


Sub nextWorkDay()
myDate = Format(Now, "w")
MoDate = Format(Now, "d")
MoSchedDate = MoDate + 1
Select Case myDate
Case Is = 1
MoSchedDate = MoDate + 1
Case Is = 6
MoSchedDate = MoDate + 3
Case Is = 7
MoSchedDate = MoDate + 2
End Select
MsgBox "The next work day is " & MoSchedDate & " " & Format(Date,
"mmmm")
End Sub
 
R

Rick Rothstein

The Excel way to do this (on the worksheet itself) would be to make use of
the Analysis ToolPak add-in...

=WORKDAY(A1,2)

This function also allows you to account for holidays via an optional 3rd
argument. But you said you wanted to be able to use the solution in Word
also... this means you want a VB solution. If the date you are adding your 2
"business" days to will never be a Saturday, you can use this statement...

Add2BusinessDays = DateIn + 2 - 2 * ((Weekday(DateIn) = 5) +
(Weekday(DateIn) >= 6))

and if your date could be a Saturday, then you can use this statement
instead...

Add2BusinessDays = DateIn + 2 - 2 * ((Weekday(DateIn) = 5) +
(Weekday(DateIn) >= 6) + 3 * (Weekday(DateIn) = 7))

Note: Your newsreader will surely word wrap those statements, but each one
should be on a single line.
 
Top