help with dates in VBA

bbrimberry

New Member
I'm relativity new to VBA. I am hopeful someone can help me with the below macro.

The Macro takes a day of the week like Monday or Tuesday and converts it to a real date based on whats imputed.

It works the way I want other than, I need it to be formatted as a 2 digit month 2 digit day and 4 digit year.
Example January 7th 2019 would be 01/07/2019.

Thanks for any help!!








Sub InputboxStuff()
Dim dte As Date
mbox = InputBox("Enter a Monday Date")
If IsDate(mbox) Then
dte = CDate(mbox)


Else
MsgBox "This isn't a date. Try Again"





End If




Dim c As Range
For Each c In Selection
Select Case UCase(c)


Case "MONDAY"
c = dte
Case "TUESDAY"
c = dte + 1


Case "WEDNESDAY"
c = dte + 2


Case "THURSDAY"
c = dte + 3


Case "FRIDAY"
c = dte + 4


End Select
Next c






End Sub
 

JoeMo

MrExcel MVP
Add the line in red font to each case.
Rich (BB code):
Case "MONDAY"
c = dte
c.NumberFormat = "mm/dd/yyyy"
 

Some videos you may like

This Week's Hot Topics

Top