View Full Version : Problem with keeping the year end dates of downloaded financial data

10-18-2012, 06:00 AM
I'm downloading a table from MSN Money that summarises key company financial data over 10 years. The first column contains the financial year end date e.g. 3/02, 3/03 .... 3/12. This date information is being interpreted by Excel 2011 (Mac) as 03-Feb 2012 thro 03-Dec 2012. Is there a way in VBA to keep the formatting as month/year i.e. 03-02 (Mar-02) thro 03-12 (Mar-12). What's important is that the year end month is maintained across the different years.

10-24-2012, 10:39 PM
What you do depends on whether you just want to ensure that the display stays the same or whether you need it as a date.
Firstly ensure that the column that is getting this date data is formatted as text and if this just needs to be displayed correctly then this is the end of it.
If you need it as a date then you can run a macro such as:

Sub text_to_date()
Dim rng As Range
Dim dPart
Set rng = Range("A1")'start position of date data
dPart = Split(rng.Value, "/")
NewDate = DateSerial(Val("20" & dPart(1)), Val(dPart(0)), 1)
rng.NumberFormat = "mm/yy" 'date
rng.Value = NewDate
Set rng = rng.Offset(1, 0)
Loop While rng.Value <> ""
End Sub

10-25-2012, 08:33 AM
Many thanks.