Results 1 to 3 of 3

Thread: Add coding to check day of the week
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Location
    Bakersfield, CA
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add coding to check day of the week

    Hello all,

    I have a coding that adds appointments to the outlook calendar. The situation is that i have a date in a cell. The coding would then make an appointment 90 days from the date entered. The issue is, what do i add to the coding so in case the 90 days falls on a Saturday or Sunday, it moves it to the following Monday. Ie, if the 90 days falls on September 21 or September 22 (Saturday or Sunday), the coding would make the appointment for September 23 or Monday.

    Code:
    dStartTime = (TPws.Cells(rw, "AD").Value + 90) + #8:00:00 AM#
    This is all i have. AD is the date entered in the spreadsheet and i just add 90. Now what do i add to check to see that that date isn't on a Saturday or Sunday and if it is, it moves it to Monday.

    Thank you.

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add coding to check day of the week

    Hi zoog25,

    Try this:

    Code:
    If Weekday(dStartTime, vbMonday) = 6 Then 'Saturday
        dStartTime = (TPws.Cells(rw, "AD").Value + 90 + 2) + #8:00:00 AM#
    ElseIf Weekday(dStartTime, vbMonday) = 7 Then 'Sunday
        dStartTime = (TPws.Cells(rw, "AD").Value + 90 + 1) + #8:00:00 AM#
    End If
    I have a coding that adds appointments to the outlook calendar.
    If you could post your entire code it would be appreciated as I'm sure setting appoints via Excel would be useful to at least some users in the community.

    Thanks,

    Robert

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Add coding to check day of the week

    If I have understood correctly, I think this should do it for you directly.
    Code:
    dStartTime = WorksheetFunction.WorkDay(TPws.Cells(rw, "AD").Value + 89, 1) + #8:00:00 AM#
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •