Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a var filled with 20100405 is possible to check if the datae is really a monday?
    Tks.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I am sure there are several solutions to this

    Here is a sub with an example

    You could make it into a Function and pass the Date

    Code:
    Sub CheckDate()
    
    Dim varDate, varRealDate, strDay As String
    
    varDate = "20100405"
    varRealDate = DateSerial(Left(varDate, 4), Mid(varDate, 5, 2), Right(varDate, 2))
    strDay = Format(varRealDate, "ddd")
    
    'You may need to change this to use the Local Language equivalent of Monday
    If strDay = "Mon" Then
        MsgBox "Monday"
    Else
        MsgBox "Not a Monday " & strDay
    End If
    
    
    End Sub
    OR as a Function that returns True or False

    Code:
    Function CheckMonday(varDate) As Boolean
    
    Dim varRealDate, strDay As String
    
    Application.Volatile
    varRealDate = DateSerial(Left(varDate, 4), Mid(varDate, 5, 2), Right(varDate, 2))
    strDay = Format(varRealDate, "ddd")
    
    'You may need to change this to use the Local Language equivalent of Monday
    If strDay = "Mon" Then
        CheckMonday = True
    Else
       CheckMonday = False
    End If
    
    End Function
    Andrew

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AKW View Post
    I am sure there are several solutions to this

    Here is a sub with an example

    You could make it into a Function and pass the Date

    Code:
    Sub CheckDate()
    
    Dim varDate, varRealDate, strDay As String
    
    varDate = "20100405"
    varRealDate = DateSerial(Left(varDate, 4), Mid(varDate, 5, 2), Right(varDate, 2))
    strDay = Format(varRealDate, "ddd")
    
    'You may need to change this to use the Local Language equivalent of Monday
    If strDay = "Mon" Then
        MsgBox "Monday"
    Else
        MsgBox "Not a Monday " & strDay
    End If
    
    
    End Sub
    OR as a Function that returns True or False

    Code:
    Function CheckMonday(varDate) As Boolean
    
    Dim varRealDate, strDay As String
    
    Application.Volatile
    varRealDate = DateSerial(Left(varDate, 4), Mid(varDate, 5, 2), Right(varDate, 2))
    strDay = Format(varRealDate, "ddd")
    
    'You may need to change this to use the Local Language equivalent of Monday
    If strDay = "Mon" Then
        CheckMonday = True
    Else
       CheckMonday = False
    End If
    
    End Function
    work perfect!

    sorry but how to calculate datediff in days between 20100405 and 20100409 ...in this case is 5
    Tks.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Could try this

    Function top and then just an example of using it in a sub below

    Note it also works on a sheet as a function

    Code:
    Function FindDateDiffDays(varStart, varEnd) As Long
    
    Dim lngDiff As Long, dteStart As Date, dteEnd As Date
    
    'NOTE there is NO error trapping in this
    Application.Volatile
    
    dteStart = DateSerial(Left(varStart, 4), Mid(varStart, 5, 2), Right(varStart, 2))
    dteEnd = DateSerial(Left(varEnd, 4), Mid(varEnd, 5, 2), Right(varEnd, 2))
    
    lngDiff = (dteEnd - dteStart) + 1
    
    FindDateDiffDays = lngDiff
    
    End Function
    
    Sub ExampleDD()
    
    Dim varStart, varEnd, lngDays As Long
    
    
    varStart = 20100405
    varEnd = 20100409
    
    lngDays = FindDateDiffDays(varStart, varEnd)
    
    MsgBox "Difference is.... " & lngDays
    
    End Sub
    Andrew

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AKW View Post
    Could try this

    Function top and then just an example of using it in a sub below

    Note it also works on a sheet as a function

    Code:
    Function FindDateDiffDays(varStart, varEnd) As Long
    
    Dim lngDiff As Long, dteStart As Date, dteEnd As Date
    
    'NOTE there is NO error trapping in this
    Application.Volatile
    
    dteStart = DateSerial(Left(varStart, 4), Mid(varStart, 5, 2), Right(varStart, 2))
    dteEnd = DateSerial(Left(varEnd, 4), Mid(varEnd, 5, 2), Right(varEnd, 2))
    
    lngDiff = (dteEnd - dteStart) + 1
    
    FindDateDiffDays = lngDiff
    
    End Function
    
    Sub ExampleDD()
    
    Dim varStart, varEnd, lngDays As Long
    
    
    varStart = 20100405
    varEnd = 20100409
    
    lngDays = FindDateDiffDays(varStart, varEnd)
    
    MsgBox "Difference is.... " & lngDays
    
    End Sub
    NATURALLY WORK!
    Tks.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Another option w/o VBA:

    Paste: =WEEKDAY(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))) =2
    into a NAME called isMonday see screen capture below
    Then use formula =isMonday (will return True or False)

    Assumptions:
    1. Formula (=isMonday) is in cell immediately to the right of the one holding the date string in yyyymmdd format.

    Note: screen capture below shows formulas B1 the inline test, B2 Named formula in use. Both return True when Monday.

    RG
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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