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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
jmcgOwnerCommented:
Try this one perhaps? From Microsoft MSDN, found via Google search on "MonthView code example".

How-to-use-MonthView
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.