Vba datepart question

JonRowland

Active Member
Joined
May 9, 2003
Messages
417
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I suppose you could use Format.
Code:
Format(DatePart("d", #01/01/2018#), "00")
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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.
 
Upvote 0
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.
 
Upvote 0
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...
Code:
[table="width: 500"]
[tr]
	[td]Cells(lngrow, 24) = Chr(96 + Month(Cells(lngrow, 2))) & Format(Cells(lngrow, 2), "_dd-mmm")[/td]
[/tr]
[/table]
 
Upvote 0
Oops, I thought the Select Case was for days not months - should have realised since it was so short.
 
Upvote 0
Rick,

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

Yes Norrie was days... :):)

Jon
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top