• Control if date is really a Monday

    Author
    Topic
    #468289

    I have a var filled with 20100405 is possible to check if the datae is really a monday?
    Tks.

    Viewing 2 reply threads
    Author
    Replies
    • #1219299

      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
      
      • #1219300

        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.

    • #1219301

      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
      
      • #1219308

        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.

    • #1219417

      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

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    Viewing 2 reply threads
    Reply To: Control if date is really a Monday

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: