Create a string using date function, with preceding zero on day and month

I am trying to create a date in VBA. I want it to display as 13.04.2018 for today's date.

However, I can currently only get it to display as 13.4.2018

How do i include a preceding zero on days and months with a single digit ?

I have currently got to:

stDateDay = Day(Date)
stDateMonth = Month(Date)
stDateYear = Year(Date)
stDateFull = stDateDay & "." & stDateMonth & "." & stDateYear

PeterH

Posted 2018-04-13T10:43:32.443

Reputation: 5 346

2strDateFull = format (Date, "dd.mm.yyyy")? – Berend – 2018-04-13T11:06:23.063

1Searching for "[microsoft-excel] date format" brings up 1148 posts. The answer to your question is likely among those. – Tom Brunberg – 2018-04-13T11:13:25.963

@TomBrunberg - that's a few less than a Google search for "vba date format" which gives 1.8 million options, the 2nd of which is MS's own docs... ;) – FreeMan – 2018-04-13T11:24:44.130

Yeah @Free there's plenty of too much :) – Tom Brunberg – 2018-04-13T11:38:32.843

@Berend Thanks, post as answer and i can accept as correct – PeterH – 2018-04-13T11:53:38.513

Answers

To format a date (or any other value) in VBA, you can use the Format function. For example:

strDateFull = Format(Date, "dd.mm.yyyy")

The second parameter is a format string that can be either a predefined format name (General Date, Long Date, Medium Date or Short Date), or a format specifier including symbols such as dd, mm or yy.

For a full list of symbols see the documentation: https://msdn.microsoft.com/en-us/VBA/language-reference-vba/articles/format-function-visual-basic-for-applications

Berend

Posted 2018-04-13T10:43:32.443

Reputation: 1 824

simply You can try this:

Range("A1").Value = Format(StDateFull, "dd.mm.yyyy")

Or you can also Format the Cell or Range of Cells before you pass StDateFull value to it/them like:

Range("A1").NumberFormat = "dd.mm.yyyy"

Range("A:C").Select
Format$ ("dd.mm.yyyy")

Range("A1").value = stDateFull

Rajesh S

Posted 2018-04-13T10:43:32.443

Reputation: 6 800

Hi Rajesh, i am not using the sting for a Cell Value, Thanks anyway – PeterH – 2018-04-13T11:44:00.150

Then where you want to use it? – Rajesh S – 2018-04-13T11:47:54.817

stDateFull = Format(Date, "dd.mm.yyyy") ActiveSheet.Name = stDateFull – PeterH – 2018-04-13T11:52:02.267

i got there in the end – PeterH – 2018-04-13T11:52:21.317

Nice, but ultimately you use it somewhere in the sheet !! – Rajesh S – 2018-04-13T11:56:18.170

Are you trying to rename the Sheet Name? – Rajesh S – 2018-04-13T12:02:10.217

Yes, each day i will be creating a new sheet, and wanted to name it with the day it was created etc. hence not needing Range.Value – PeterH – 2018-04-13T13:59:03.767