Vba datepart question

[JonRowland]

Hi,

I have a query regarding DartPart and the format of the value it returns.

My example is using date 01/01/2018

If I use DatePart("d", #01/01/2018#) the value is returned is 1. However, what I would like is to have the value, if less than ten, to have a proceeding zero, eg 01.

How would I go about this?

Jon

 

[Norie]

I suppose you could use Format.

Format(DatePart("d", #01/01/2018#), "00")

 

[JonRowland]

Doesn't seem to work.

Probably best to show my incomplete code that I'm actually using.

<code>

Sub MyCode()

' Declare Variables
Dim lngrow As Long, lngLastRow As Long, lngStartRow As Long
Dim intDateDay As Integer, intDateMonth As Integer
Dim DateDayMonth As String

lngStartRow = 2
lngLastRow = Cells.Find("*", Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
For lngrow = lngStartRow To lngLastRow Step 1

<b>' If the Day was 1st of the month, this would return the value 1 for 2nd then 2 etc. I would like 01, 02 etc for all values less than 10.
intDateDay = DatePart("d", Cells(lngrow, 2)) ' Give me Day Value

intDateMonth = DatePart("m", Cells(lngrow, 2)) ' Give me Month Value

DateDayMonth = intDateDay & "-" & MonthName(intDateMonth, True) ; MonthName converts Month Number to Month Short Nam (1=Jan, 2=Feb etc)



Select Case intDateMonth

Case 1
Cells(lngrow, 24) = "a_" & DateDayMonth

Case 2
Cells(lngrow, 24) = "b_" & DateDayMonth

Case 3
Cells(lngrow, 24) = "c_" & DateDayMonth

Case 4
Cells(lngrow, 24) = "d_" & DateDayMonth

Case 5
Cells(lngrow, 24) = "e_" & DateDayMonth

Case 6
Cells(lngrow, 24) = "f_" & DateDayMonth

Case 7
Cells(lngrow, 24) = "g_" & DateDayMonth

Case 8
Cells(lngrow, 24) = "h_" & DateDayMonth

Case 9
Cells(lngrow, 24) = "i_" & DateDayMonth

Case 10
Cells(lngrow, 24) = "j_" & DateDayMonth

Case 11
Cells(lngrow, 24) = "k_" & DateDayMonth

Case 12
Cells(lngrow, 24) = "l_" & DateDayMonth

End Select

Next lngrow

End Sub
</code>

 

[Rick Rothstein]

Doesn't seem to work.

Probably best to show my incomplete code that I'm actually using.

<code> Dim intDateDay As Integer, intDateMonth As Integer

<b>' If the Day was 1st of the month, this would return the value 1 for 2nd then 2 etc. I would like 01, 02 etc for all values less than 10.
intDateDay = DatePart("d", Cells(lngrow, 2)) ' Give me Day Value
</code>

The problem is you are assigning the formatted value to intDateDay which you have declared as Integer... integer values are numbers and numbers do not have leading zeroes... if you declare that variable as String, then it will retain the leading zero that the Format function put in it.

 

[Norie]

What are you trying to do with your code?

If you want to get a date in a specific format there are probably better ways than splitting it apart using DatePart and then joining it up again, for example the Format function I've already suggested.

For example to get the date from Cells(lngrow, 2) in DD-MMM format you could use this.

DateDayMonth =Format(Cells(lngrow, 2), "dd-mmm")

Pretty sure something could be done with the Select Case too but not sure what would happen for days past 26.

 

[JonRowland]

Norie,


This actually does exactly what I wanted & saves breaking the date up and re-joining.

<CODE>DateDayMonth =Format(Cells(lngrow, 2), "dd-mmm")</CODE>

What I am doing is taking a date (dd/mm/yy) and using the dd-mmm and adding a letter in front dependant upon the month, hence the 12 Case. Basically to import into another program which is quirky and doesn't order dates correctly but adding a letter in front sorts out that issues.

Thanks for your help.

 

[Norie]

What do you add for days 26-31?

 

[Rick Rothstein]

Norie,


This actually does exactly what I wanted & saves breaking the date up and re-joining.

<CODE>DateDayMonth =Format(Cells(lngrow, 2), "dd-mmm")</CODE>

What I am doing is taking a date (dd/mm/yy) and using the dd-mmm and adding a letter in front dependant upon the month, hence the 12 Case.

You do not need the Select Case structure to do that, this single line of code inside your For..Next loop should be all that you need...

[table="width: 500"]
[tr]
	[td]Cells(lngrow, 24) = Chr(96 + Month(Cells(lngrow, 2))) & Format(Cells(lngrow, 2), "_dd-mmm")[/td]
[/tr]
[/table]

 

[Norie]

Oops, I thought the Select Case was for days not months - should have realised since it was so short.

 

[JonRowland]

Rick,

Thanks for that....that's much much better than the 12 Select Case.

Yes Norrie was days...

Jon