help with dates in VBA

bbrimberry

New Member
Joined
Mar 23, 2016
Messages
34
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Add the line in red font to each case.
Rich (BB code):
Case "MONDAY"
c = dte
c.NumberFormat = "mm/dd/yyyy"
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top