Validating Start and End Dates

I'm often using date specific queries to manage reports in excel and have found it handy to validate the start and end date to make sure that I'm entering the right thing !

Following the module using the date function : in this case I'm using the variables FromDate and ToDate


Public Sub GetValidDates(ByRef FromDate As Date, _
                         ByRef ToDate As Date, _
                         Optional MinDate As Date, _
                         Optional MaxDate As Date)
    Dim bError As Boolean
    Dim sErrorMessage As String
    Dim vMyStart As Variant, vMyEnd As Variant

    '----------------------------------------------------------------------------
    ' Code below taken with advice from
    ' http://www.mrexcel.com/forum/showthread.php?t=274520
    '----------------------------------------------------------------------------

    vMyStart = ""
    vMyEnd = ""
    Do
        bError = False
        sErrorMessage = ""
        vMyStart = Application.InputBox(Prompt:="Enter Month Start date (dd/mm/yy)", _
                                             Title:="Start Date", _
                                             Default:=vMyStart)

        If IsDate(vMyStart) = False Then
            bError = True
            vMyStart = ""
            sErrorMessage = "Start date not a date"
        Else
            If CheckDateInRange(Datex:=vMyStart, _
                                MinDate:=MinDate, _
                                MaxDate:=MaxDate) = False Then
                bError = True
                sErrorMessage = "Month start Date is not in range"
                vMyStart = ""
            End If

            If bError = False Then
                vMyEnd = Application.InputBox(Prompt:="Enter Month End date (dd/mm/yy)", _
                                                   Title:="End Date", _
                                                   Default:=vMyEnd)
                If IsDate(vMyEnd) = False Then
                    bError = True
                    sErrorMessage = "End date is not a date"
                    vMyEnd = ""
                End If
            End If

            If bError = False Then
                If CheckDateInRange(Datex:=vMyEnd, _
                                    MinDate:=MinDate, _
                                    MaxDate:=MaxDate) = False Then
                    bError = True
                    sErrorMessage = "End Date is not in range"
                    vMyEnd = ""
                End If
            End If

            If vMyEnd < vMyStart Then
                bError = True
                sErrorMessage = "Start Date not before End date"
                vMyEnd = ""
            End If

        End If
        If bError Then MsgBox Prompt:=sErrorMessage, Buttons:=vbOKOnly + vbCritical, Title:="Invalid Date!"
    Loop While bError

    FromDate = CDate(vMyStart)
    ToDate = CDate(vMyEnd)
End Sub
Private Function CheckDateInRange(ByVal Datex As Variant, _
                                  Optional MinDate As Date, _
                                  Optional MaxDate As Date) As Boolean
'----------------------------------------------------------------------------
' Return False if specified date is not in range
'----------------------------------------------------------------------------
    Dim datCur As Date

    On Error GoTo labError
    datCur = CDate(Datex)
    If Not (IsMissing(MinDate)) Then
        If datCur < MinDate Then
            CheckDateInRange = False
            Exit Function
        End If
    End If

    If Not (IsMissing(MaxDate)) Then
        If datCur > MaxDate Then
            CheckDateInRange = False
            Exit Function
        End If
    End If

    CheckDateInRange = True
    Exit Function

labError:
    CheckDateInRange = False
End Function

Adding up totals dependant on flags

There has been an occasion where I have need to add up entries from a list depending if a certain condition has been met. In this occasion it was adding up which hours were overtime or not. In this case I ended the overtime hours with a £

Where A1:A5 data equals

1£
2
2£
2£

{=SUM(1*IF(RIGHT(A$1:A$5,1)="£",MID(A$1:A$5,2,LEN(A$1:A$5)-2),0))} = 5

*note
This is an array formula so use Ctrl Shift & Enter to add curly brackets

Adding and Calculating with Dates

If you want to find out what the date would be in X days/months/years time then this formula can help.

Where
A1 = 01/01/2012

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1) = 02/01/12
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) = 01/02/12
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) = 01/01/13

Simply add in a cell reference for the days/months/years to add and this is a very powerful formula