Hi Andy,
Try using the Format() function. Try with this example code.
Sub test()
' Extract date, month and year
sDate = Cells(1, 1)
'comment out this line and try adding date to cell A1
sDate = "07/10/2011"
sExtractDD = Format(sDate, "dd") 'date as 2 digits
sExtractD = Format(sDate, "d") 'date as 1 digit
sExtractMM = Format(sDate, "mm") 'month as number
sExtractMMM = Format(sDate, "mmm") 'month as 3 letters
sExtractMMMM = Format(sDate, "mmmm") 'month as full name
sExtractYYYY = Format(sDate, "yyyy") 'year as 4 digits
sExtractYY = Format(sDate, "yy") 'year as 2 digits
'mixed output results
Debug.Print sExtractDD
Debug.Print sExtractMM
Debug.Print sExtractYYYY
Debug.Print
Debug.Print sExtractD
Debug.Print sExtractMMM
Debug.Print sExtractYY
Debug.Print
Debug.Print sExtractDD
Debug.Print sExtractMMMM
Debug.Print sExtractYYYY
Debug.Print "--"
End Sub
Good luck, Otto
--- In ExcelVBA@yahoogroups.com, "andycoulthart" <andycoulthart@...> wrote:
>
> Hi,
> I'm trying to extract part of a date (as it's displayed) but can only access the underlying Excel number code.
> On a worksheet I enter =Date() in a cell formatted as mm/dd/yyyy (displayed as 10/27/2011).
> I now try to extract the day (27) as the number 27.
> I've tried Mid, Left etc and Paste Special but these only return part of the date code and also tried formatting cells as text - to no avail.
> Is there a macro I can write which can achieve this?
>
> Many thanks,
> Andy
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar