CODEWITHSUNDEEP
excelvbaformattingstring-formatting
Chad Portman
Chad Portman

Reputation: 1216

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?

Upvotes: 20

Views: 135074

Answers (4)

GFunk
GFunk

Reputation: 41

I know it's old, but, to answer the question as clarified, I would use in the built in date formatting functionality.

To modify DeanOC's answer:

Sub Macro1()
    Dim dateDate As Date
    Dim strDate  As String
    Dim strDay  As String

    dateDate = "2015-5-1"
    strDate = Format(dateDate, "mm/dd/yy") ' = "05/01/15"
    strDay = Format(dateDate, "dd")        ' = "01"

    MsgBox "The two digit day of """ & strDate & """ is """ & strDay & ""."
End Sub

Upvotes: 4

PKatona
PKatona

Reputation: 639

Sure you can format an integer, you just convert it to string within the format command:

formattedIntAsString = Format(Cstr(intValue), "00")

Upvotes: 8

Matti
Matti

Reputation: 41

I did it like this:

number_item = 2
number_item = WorksheetFunction.Text(number_item, "00")

This will do the job.

Upvotes: 4

DeanOC
DeanOC

Reputation: 7282

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

Upvotes: 37

Related Questions