How to get excel VBA to format date

Question

I am making a GUI in excel that allows the user to enter information into the spreadsheet just by using the GUI. I have run into a problem however. I can't seem to get the date to format right. this is the code I have for it (it being the code for the GUI:

Dim AN As Worksheet
Set AN = ThisWorkbook.Sheets("Sheet2")
Dim n As Long

n = AN.Range("A" & Application.Rows.Count).End(xlUp).Row

'AN.Range("A" & n + 1).Value ='
AN.Range("B" & n + 1).Value = Me.yyyy.Value + "-" + Me.mm.Value + "-" + Me.dd.Value
'AN.Range("C" & n + 1).Value ='
AN.Range("D" & n + 1).Value = Me.N_O_B.Value
AN.Range("E" & n + 1).Value = Me.Address_street.Value
AN.Range("F" & n + 1).Value = Me.City_info.Value
AN.Range("G" & n + 1).Value = Me.State_info.Value
AN.Range("H" & n + 1).Value = Me.Zip_info.Value
AN.Range("I" & n + 1).Value = Me.Lname.Value
AN.Range("J" & n + 1).Value = Me.Fname.Value
AN.Range("K" & n + 1).Value = Me.Minitial.Value
AN.Range("L" & n + 1).Value = Me.Suffix.Value
AN.Range("P" & n + 1).Value = Me.Busi_zip.Value
AN.Range("X" & n + 1).Value = Me.Shrt_Descript.Value

I am trying to get the date to show up in column B. It is showing up in column B, but not the way I thought it would. So in the GUI you enter the year, the month, and the date. Then I tried to make the code take the information and put it in a YYYY/MM/DD format but its not working. I have to have it in this format. Any advice would be helpful.

Lets say I enter todays date. so The system in theory should output 2018/06/14 but instead outputs the number "43265" instead of the date. — R3DSTON3R
– R3DSTON3R, Commented Jun 14, 2018 at 14:07
Then just format the cell to the proper format, see @Jeeped's answer below. The second line formats the date to the proper format. — Scott Craner
– Scott Craner, Commented Jun 14, 2018 at 14:09

user4039065 · Accepted Answer · 2018-06-14 13:59:00Z

1

Force the date's raw value and then format the cell.

AN.Range("B" & n + 1).Value = dateserial(Me.yyyy.Value, Me.mm.Value, Me.dd.Value)
AN.Range("B" & n + 1).numberformat = "yyyy-mm-dd"

btw, the correct string concatenation operator is an ampersand (e.g. &). While a plus sign may work, it is best avoided, particularly when working with numbers.

answered Jun 14, 2018 at 13:59

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

R3DSTON3R Over a year ago

This worked! Also thank you for telling me to avoid the plus sign. I didnt know that.

Collectives™ on Stack Overflow

How to get excel VBA to format date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related