Control if date is really a Monday

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote