-1

New to both NiceLabel and VBScript. I have an expiration date string which requires leading zero's when necessary.

For example;

"2016-2-7" to "2016-02-07"
"2016-2-27" to "2016-02-27"

I'm guessing the best way is to:

  1. Determine the number of digits (n) in the value (v) via string conversion and length method
  2. if n < 2: v = "0"+ v;

however I cannot figure out how to do this.

Any help will be greatly appreciated.

user692942
  • 16,398
  • 7
  • 76
  • 175
Dan Serio
  • 11
  • 1

2 Answers2

0

If this is VBScript then just break it up and pad it.

Dim LabelDate, FormattedDate

LabelDate = #2/7/2016#
FormattedDate = Right("00" & DatePart("d", LabelDate), 2) & "-" & _
                Right("00" & DatePart("m", LabelDate), 2) & "-" & _
                DatePart("yyyy", LabelDate)

'Test conversion
Call MsgBox(LabelDate & " to " & FormattedDate)

If you really want to break it down, you can do;

Dim LabelDate, FormattedDate
Dim LabelMonth, LabelMonth, LabelYear

LabelDate = #2/7/2016#
LabelYear = DatePart("yyyy", LabelDate)
LabelMonth = Right("00" & DatePart("m", LabelDate), 2)
LabelDay = Right("00" & DatePart("d", LabelDate), 2)

'Restructure them how you want...
FormattedDate =  LabelDay & "-" & LabelMonth & "-" LabelYear         

'Test conversion
Call MsgBox(LabelDate & " to " & FormattedDate)

Yet another approach to make the restructure a bit nicer is to use an Array().

Dim LabelDate, FormattedDate
Dim LabelDateArray

LabelDate = #2/7/2016#
LabelDateArray = Array( _
                   Right("00" & DatePart("d", LabelDate), 2), _
                   Right("00" & DatePart("m", LabelDate), 2), _
                   DatePart("yyyy", LabelDate) _
                 )

'Restructure using the Array...
FormattedDate = Join(LabelDateArray, "-")        

'Test conversion
Call MsgBox(LabelDate & " to " & FormattedDate)

Just to explain what's going on

In the end you are wanting to take a date literal and format it for display purposes. Padding is actually simpler then it first appears, all you need to do is pre-append "00" to the front of the value then append the specific DatePart() (only needed for Month and Day). Then you use a simple hack to get the desired result using the Right() function we take 2 characters from the left, which if you value is 1 character applies the "0" padding.

Apparently it takes another answer to point out this out

Hardly relevant but as this answer highlights, you can also use

  1. Month(LabelDate) instead of DatePart("m", LabelDate)
  2. Day(LabelDate) instead of DatePart("d", LabelDate)

Both functions do the same thing and I don't know of any overriding reason to use one or the other, except maybe Month(LabelDate) is shorter to write then DatePart("d", LabelDate). The above method will work using either so it's negligible.

Why do some examples use Right("0" & not Right("00" & instead?

It's simple, they are assuming you will always have a value for the Month and Day but as I have had in the past you won't always pass those values in from system functions, so for the occasions where a null value is passed it will ensure that the date part is set to 00.

Also it worth pointing out that what all these solutions produce is a string it's no longer a date so if you are going to perform date functions against it make sure, you either store the original value and use that or use CDate() to convert the value to a valid date literal.

Useful Links

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
0
  1. Extract the month using the Month function (or the day using the Day function).
  2. Prepend a 0 to the result.
  3. Use the Right function to extract the last two characters.

If the month is 2, this will prepend 0 to give 02. Taking the two right-most characters will result in 02.

If the month is 12, you get 012, and the two right-most characters are 12.

Below is an example.

MyDate = #2016-2-27#
MyMonth = Right("0" & Month(MyDate), 2)
MyDay = Right("0" & Day(MyDate), 2)
FormattedDate = Year(MyDate) & "-" & MyMonth & "-" & MyDay
MsgBox FormattedDate
aphoria
  • 19,796
  • 7
  • 64
  • 73
  • How is this different to [my answer](http://stackoverflow.com/a/35060805/692942)? – user692942 Jan 29 '16 at 13:20
  • The separator I just notice was different, wow took a whole answer to just place that in a comment and I would have corrected the answer. – user692942 Jan 29 '16 at 13:22
  • Btw `Right("0" & ` is fine if you the value you are checking always exists, reason I use `Right"00" & ` to accommodate null values. – user692942 Jan 29 '16 at 13:23
  • @Lankymart I was typing my answer and submitted before I saw yours. The code in your original answer didn't work, so I left mine. – aphoria Jan 29 '16 at 15:20
  • Yes, my code sample assumes a valid date to begin with. Whether you add `0` or `00` to the beginning, you'll have to add some error-handling either to ensure you're starting with a valid date or to check the output. What do you do if the month or day returns `00`? – aphoria Jan 29 '16 at 15:24
  • It's not just `Date` values it's useful for `Time` as well. – user692942 Jan 29 '16 at 15:29