I have this problem which has me stumped.
I want to show a date in the excel cell in the format "Jan-13" (for 1st January 2013). Instead I get an integer. The integer does not correspond to the date (41275 in this case), instead the integer represents, in this example, 13th January 2012 (40921). I cannot for the life of me understand where this number comes from.
Here's the code (not optimised, rather set up to debug this issue!). :
Dim Month As Range
Dim r As Integer
Dim Temp As Variant
Set Month = Application.InputBox(Prompt:= _
"Please select a range of months from a COLUMN", _
Title:="SPECIFY MONTHS", Type:=8)
r = 1
With Worksheets("MyWorksheetName")
' Displays, e.g. "41275" - good
MsgBox ("Month: " & Month.Value2(x, 1))
' Displays, e.g. "Jan-13" - good
MsgBox ("Formatted Month: " & Format(Month.Value2(x, 1), "MMM-yy"))
Temp = Format(Month.Value2(x, 1), "MMM-yy")
' Displays, e.g. "Jan-13" - good
MsgBox ("Temp: " & Temp)
' "howdy" appears as entry in cell A1 in Excel spreadsheet - good
.Cells(r, 1) = "howdy"
' e.g. "40921" appears as entry in cell A1 in Excel spreadsheet - BAD!
.Cells(r, 1) = Temp
' Displays, e.g. "40921" - BAD!
MsgBox ("Cells Month value: " & .Cells(r, 1))
Another interesting point. If I assign the value to .Cells(r, 6), i.e. to a different column, then I get the same value, but it shows in Excel in a different format, "dd-MMM".
Any help understanding this would be much appreciated.
Mailbags
Bookmarks