We help IT Professionals succeed at work.

Excel Date input

swjtx99
swjtx99 asked
on
Hi,

I have the following code that inserts today's date into an input box. The problem is that it's defined to 10 characters which would be max for MM/DD/YYYY but when it's 3/1/2015, that is only eight characters so the ninth will be blank but the  10th will be the hour example: 3/1/2016 9  (if it's 9 am or pm)

That's messing things up considerably, Any way to define the format ?

Sub Step_2_InputTargetDate()

    Dim InputDate As String, InputPrompt As String
    Dim Holidays() As Variant, i As Integer, j As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("Holidays")
    Holidays = ws.Range("Holidays")
    Sheets("Backlog Report").Select
       
    'Input the target date
    InputPrompt = "Input a target date"
    InputDate = Left(CStr(Now), 10) 'Here 10 characters are too many if the date can be expressed with 8 characters
    Do
        InputDate = InputBox(InputPrompt, "Target date", InputDate)
        If Len(InputDate) = 0 Then
            MsgBox "No value, program end"
            End
        End If
        On Error Resume Next
        targetDate = DateValue(InputDate)
        On Error GoTo 0
        If targetDate = 0 Then
            InputPrompt = "Not a valid date, try again"
        Else
            If Weekday(targetDate, vbMonday) = 6 Then
                InputPrompt = "Date is a Saturday, try again"
                targetDate = 0
            ElseIf Weekday(targetDate, vbMonday) = 7 Then
                InputPrompt = "Date is a Sunday, try again"
                targetDate = 0
            Else
                j = 0
                For i = LBound(Holidays, 1) To UBound(Holidays, 1)
                    If targetDate = Holidays(i, 1) Then
                        j = 1
                    End If
                Next i
                If j = 1 Then
                InputPrompt = "Date is a Holiday, try again"
                targetDate = 0
                End If
            End If
        End If
    Loop While targetDate = 0

Thanks in advance,

swjtx99
Comment
Watch Question

Wouldn't you rather use a date picker? With the MonthView control, for instance, your user doesn't have to type in a date, they select one from a pop-up calendar.

Author

Commented:
It's easier if the current date is populated as that will be the selection 90% of the time so they just click "OK". Occasionally a user will want to change it. If the MonthView control will do that then great. Do you have a code example?

Thanks,

swjtx99
Try this one perhaps? From Microsoft MSDN, found via Google search on "MonthView code example".

How-to-use-MonthView
Most Valuable Expert 2011
Top Expert 2011
Commented:
You could just use:

InputDate = Date

which will default to the user's date format or if you want it specifically formatted, use:

InputDate = Format(Date, "mm/dd/yyyy")
I have reworked your sub to make it more user friendly and also to be more flexible offer you all the input and feedback and also to ensure you will get correct data at the end. I have added a msgbox at the end if it exit the loop to show you the date that has passed and is ok and will be stored in TargetDate variable. I am using also the function Isdate to make sure the date is a valid date. You can also have the flexibility to enter a date such as 2/25 without the year. When the date is rejected you will get the prompt showing you the non valid date in the prompt and also it will reset the default date as it was outside the loop now put back in the loop.

Last but not least in your loop to check for holidays it is useless to keep looping when you found that the date is a holiday so I added Exit For after J=1


Check it and let me know.
give it all the possible values like 1/1/20340 or something like 'jojo' or 15/15 etc ... and see results.

Sub Step_2_InputTargetDate()

     Dim InputDate As String, InputPrompt As String
     Dim Holidays() As Variant, I As Integer, J As Integer
     Dim TargetDate
     Dim ws As Worksheet
     Set ws = Worksheets("Holidays")
     Holidays = ws.Range("Holidays")
     Sheets("Backlog Report").Select
        
     'Input the target date
     InputPrompt = "Input a target date"
     'InputDate = Left(CStr(Now), 10) 'Here 10 characters are too many if the date can be expressed with 8 characters
     
     Do
         InputDate = Format(Now, "mm/dd/yyyy")
         InputDate = InputBox(InputPrompt, "Target date", InputDate)
         TargetDate = InputDate
         
         If Len(TargetDate) = 0 Then
             MsgBox "No value, program end"
             End
         End If
         
         If Not IsDate(Format(TargetDate, "mm/dd/yyyy")) Then
             InputPrompt = TargetDate & " Not a valid date, try again"
             TargetDate = 0
         Else
             If Weekday(TargetDate, vbMonday) = 6 Then
                 InputPrompt = TargetDate & " this date is a Saturday, try again"
                 TargetDate = 0
             ElseIf Weekday(TargetDate, vbMonday) = 7 Then
                 InputPrompt = TargetDate & " this date is a Sunday, try again"
                 TargetDate = 0
             Else
                 J = 0
                 For I = LBound(Holidays, 1) To UBound(Holidays, 1)
                     If TargetDate = Holidays(I, 1) Then
                         J = 1
                         Exit For
                     End If
                 Next I
                 
                 If J = 1 Then
                    InputPrompt = TargetDate & "this date is a Holiday, try again"
                    TargetDate = 0
                 End If
             End If
         End If
         
         '---> Affect the date in case entered as day/month
         If TargetDate <> 0 Then TargetDate = DateValue(TargetDate)
     Loop While TargetDate = 0


MsgBox "The date entered is: " & TargetDate

End Sub

Open in new window



gowflow

Author

Commented:
Hi GowFlow,

I tried your solution and it  got rid of the extra character at the end of the date but this is one step of a multi-step macro and further along something interfered with other date functions which threw off a bunch of calculations.

Hi Rory,

Simple but effective. Your idea worked and didn't create any issues in later steps!

Hi JMCG,

I like the idea of the date picker. Thanks  for the suggestion but without reworking  a bunch of code, Rory's idea solved the problem.

Thanks to all of you for your time and assistance. Very much appreciated.

swjtx99

Author

Commented:
Simple and effective!