Vba datepart question

JonRowland

Active Member
Joined
May 9, 2003
Messages
376
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
I suppose you could use Format.
Code:
Format(DatePart("d", #01/01/2018#), "00")
 

JonRowland

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

JonRowland

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What do you add for days 26-31?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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]
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Oops, I thought the Select Case was for days not months - should have realised since it was so short.
 

JonRowland

Active Member
Joined
May 9, 2003
Messages
376
Office Version
  1. 365
Platform
  1. Windows
Rick,

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

Yes Norrie was days... :):)

Jon
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,758
Members
414,171
Latest member
12Rev79

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
Top