Excel Date input

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
swjtx99Asked:
Who is Participating?
 
Rory ArchibaldCommented:
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")
0
 
jmcgOwnerCommented:
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.
0
 
swjtx99Author 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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

How-to-use-MonthView
0
 
gowflowCommented:
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
0
 
swjtx99Author 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
0
 
swjtx99Author Commented:
Simple and effective!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.