# Date formatting

#### snowboy001

##### Board Regular
Is there any way to format a cell so that it not only shows a date as mm/dd/yyyy in the cell, but also contains the value as mm/dd/yyyy? I have some formulas set up to pull the mm, the dd, and the yyyy and concatenate them together, but when the month or day is only a single digit, the =LEFT() and =MID() formulas pull in "1/" and "/2" respectively... I need them to pull "01" and "02" instead. Any ideas?

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Not exactly sure of your request but I used the text command in this way

Excel 2010
FG
905-Aug-8605/08/1986 08/05/1986
Sheet2
Cell Formulas
RangeFormula
G9=TEXT(F9,"dd/mm/yyy") & " "&TEXT(F9,"mm/dd/yyy")

I ended up having to do this through VBA. Here is the snippet of code that I used:

Code:
``````''set values for dMM, dDD, and dYY
Select Case Mid(RCAList.Cells(iRow, 7), 2, 1)
Case "/"
dMM = "0" & Left(RCAList.Cells(iRow, 7), 1)
Case Else
dMM = Left(RCAList.Cells(iRow, 7), 2)
End Select

Select Case Mid(RCAList.Cells(iRow, 7), 2, 1)
Case "/"
If Mid(RCAList.Cells(iRow, 7), 4, 1) = "/" Then
dDD = "0" & Mid(RCAList.Cells(iRow, 7), 3, 1)
Else
dDD = Mid(RCAList.Cells(iRow, 7), 3, 2)
End If
Case Else
If Mid(RCAList.Cells(iRow, 7), 5, 1) = "/" Then
dDD = "0" & Mid(RCAList.Cells(iRow, 7), 4, 1)
Else
dDD = Mid(RCAList.Cells(iRow, 7), 4, 2)
End If
End Select

dYY = Right(RCAList.Cells(iRow, 7), 2)``````

Basically it looks for where the "/"s are at and then works from there. I am using the sections of the date to name a file through VBA anyways, so it helped to do it through there anyways.

Thanks!

Replies
7
Views
495
Replies
2
Views
110
Replies
3
Views
319
Replies
5
Views
238
Replies
6
Views
181

1,196,292
Messages
6,014,512
Members
441,826
Latest member
roudarreza

### 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.

### Which adblocker are you using?

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

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