Using DateAdd function to add 1 weekday/workday
 

Hello all,

When running the following code today (Friday, 12/1/2006), I get a message box with tomorrows date in it:

msgbox dateadd("w",1,date())

using the "w" for weekday, what is the definition of "weekday"? I was assuming that "weekday" meant M-F, not Sat or Sun.

I'm trying to get this so it will show the following Monday's date if it is run on a Friday. Running it on a M-Th will just show
the date of the next day.

What is going on here? How can I get this to skip the weekends?

--
Thanks for any help anyone can provide,

Conan Kelly

Using DateAdd function to add 1 weekday/workday
 

Jim,

Thanks for the feedback, but I'm still wondering what "w"/weekday means in regards to to the DateAdd function. Does weekday mean
M-F or does it mean all 7 days of the week? If it means the latter, then what is the difference between "w"/weekday and "d"/day.

Thanks again for all of your help,

Conan


"Jim Jackson" wrote in message ...
This will give the next workday.

Sub GetNextWorkday()
Range("A1").Activate
ActiveCell = "=TEXT(I2,""ddd"")"
x = ActiveCell.Value
If x = "Fri" Then
MsgBox DateAdd("w", 3, Date)
elseif x = "Sat"
MsgBox DateAdd("w", 2, Date)
Else
MsgBox DateAdd("w", 1, Date)
End If
End Sub
--
Best wishes,

Jim


"Conan Kelly" wrote:

Hello all,

When running the following code today (Friday, 12/1/2006), I get a message box with tomorrows date in it:

msgbox dateadd("w",1,date())

using the "w" for weekday, what is the definition of "weekday"? I was assuming that "weekday" meant M-F, not Sat or Sun.

I'm trying to get this so it will show the following Monday's date if it is run on a Friday. Running it on a M-Th will just show
the date of the next day.

What is going on here? How can I get this to skip the weekends?

--
Thanks for any help anyone can provide,

Conan Kelly

Using DateAdd function to add 1 weekday/workday
 

Jim,

Thanks for the feed back.

Sorry for being a pain, but the first part of your response doesn't pertain to my question, if I'm not mistaken.

"Weekday" uses 1 through 7 to indicate Sunday/Saturday respectively.
This pertains to the WEEKDAY function.

"d" refers to the day of the month (1 - 31)
"dd" gives a two digit day (01, 02 etc.)
"ddd" - "Mon" etc
These pertain to the format strings used in the FORMAT function.





What I would like to know is what is the difference between "w" and "d" when used as INTERVAL argument options in the DateAdd
function

If I run either of these 2 lines of code today (Fri, 12/1/2006), I get the same response, 12/2/2006 (Sat):

msgbox dateadd("d",1,date())
msgbox dateadd("w",1,date())

Obviously I'm wrong, but according to the Help file on the DateAdd function, I would think that "d" as the INTERVAL argument would
return Saturday's date and "w" as the INTERVAL argument would return Monday's date. That is not the case. It appears that there is
no difference between "d" and "w". Why would they put in 2 options that do the exact same thing? In the Help file, it says that
"d" means Day and "w" means Weekday. I translate that to: "d" will add/subtract the specified number of days to the start date
(including Saturdays & Sundays), "w" will add/subtract the specified number of WEEKDAYS to the start date (EXCLUDING Saturdays &
Sundays).

Am I wrong in thinking that? If I am, please correct me and set me straight.

Once again, sorry for being a pain. This is just annoying me and I want to know what the deal is.

Thanks again for all of your help,

Conan


"Jim Jackson" wrote in message ...
"Weekday" uses 1 through 7 to indicate Sunday/Saturday respectively.
"d" refers to the day of the month (1 - 31)
"dd" gives a two digit day (01, 02 etc.)
"ddd" - "Mon" etc

In light of this you could actually try:

If "w" = 6 then
msgbox dateadd("w",3,date())
end if
--
Best wishes,

Jim


"Conan Kelly" wrote:

Jim,

Thanks for the feedback, but I'm still wondering what "w"/weekday means in regards to to the DateAdd function. Does weekday mean
M-F or does it mean all 7 days of the week? If it means the latter, then what is the difference between "w"/weekday and "d"/day.

Thanks again for all of your help,

Conan


"Jim Jackson" wrote in message ...
This will give the next workday.

Sub GetNextWorkday()
Range("A1").Activate
ActiveCell = "=TEXT(I2,""ddd"")"
x = ActiveCell.Value
If x = "Fri" Then
MsgBox DateAdd("w", 3, Date)
elseif x = "Sat"
MsgBox DateAdd("w", 2, Date)
Else
MsgBox DateAdd("w", 1, Date)
End If
End Sub
--
Best wishes,

Jim


"Conan Kelly" wrote:

Hello all,

When running the following code today (Friday, 12/1/2006), I get a message box with tomorrows date in it:

msgbox dateadd("w",1,date())

using the "w" for weekday, what is the definition of "weekday"? I was assuming that "weekday" meant M-F, not Sat or Sun.

I'm trying to get this so it will show the following Monday's date if it is run on a Friday. Running it on a M-Th will just
show
the date of the next day.

What is going on here? How can I get this to skip the weekends?

--
Thanks for any help anyone can provide,

Conan Kelly