Search code examples
excelvbaformattingstring-formatting

Formatting a number to two digits even if the first is a 0 vba

I would like to be able to use VBA to display any number between 1-24 as a 2 digit number. Obviously the only ones that have a problem with this are 1-9 which I would like displayed as 01, 02, 03, etc. Is there a way to perform this?

Solution

  • You cannot format an integer variable, you need to use a string variable for formatting.

    You can convert the day part of a date to a format with leading zeros using the Day function to extract the day number from the date, and then using the Format function with a "00" format to add a leading zero where necessary

    Format(Day(myDate), "00")

    myDate is a Date variable containing the full Date value

    The following macro can be used as a working sample

    Sub Macro1()
    Dim myDate As Date

    myDate = "2015-5-1"

    Dim dayPart  As String

    dayPart = Format(Day(myDate), "00")

    MsgBox dayPart
End Sub