Hi All
The problem with using the format function to extract date and time components is that the regional settings of the PC determine the results.
In Otto's example sDate = "07/10/2011" is 7th October in the UK and 10th July in America.
If the PC is in Russia then Format(sDate, "ddd") gives you пят.
I could go on, and on.
I found this out the hard way when writing shared spreadsheet applications in a multi-national company with offices in almost every country in the world.
In the PC the date is always held internally as the number of days since 01-01-1900. The display of this is cosmetic.
Regards
Del +++
>
>>________________________________
>From: Otto <ottomathisen@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Tuesday, 8 November 2011, 15:57
>Subject: [ExcelVBA] Re: extracting date display
>
>
>
>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
>>
>
>
>
>
>
[Non-text portions of this message have been removed]
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