Problems assigning date data to cells

[Mailbags]

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

[martindwilson]

did you read the forum rules re code tags?

[Mailbags]

OK, thanks for the tip. Here is is again:
----

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!). :

Please Login or Register  to view this content.

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

[protonLeah]

You should have posted enough code to compile and run. Actually, a sample workbook is needed rather than guessing what's in it.

[Mailbags]

OK, bear with me. I am rushing this too much. Sorry, I realise its not very helpful.

Try this workbook:
- Run the "Combinations" macro.
- when prompted, select at least two months from the "Constants" page (suggest Jan-13 and Feb-13)
- Output appears on the MyWorksheet page

Test_Combinations.xlsm

Full macro code:

Please Login or Register  to view this content.

thanks.

[JapanDave]

Try this.

Please Login or Register  to view this content.

[Mailbags]

That helps a little, in that the correct date is now entered into the cell. However the format is not what I require (d-MMM).

So I tried changing the final write to the cell, your:
.Cells(r, 1).Value = Temp

to
.Cells(r, 1).Value = Format(Temp, "MMM-yy")

... with the result that the format is unchanged but now the date is incorrect again. So in the example of 1st Jan 2013, with your code, the cell shows "1-Jan", with my change is says "13-Jan".

(by the way, using .Value here makes no difference in either case; leave it out and its the same result)


There is something going on with the Format command and its return value which I dont understand.

[JapanDave]

How much are you going to pay me for this!!!!
Here in Japan , date is a big pain in the butt, as it is different to every where else in the world, so I feel your pain. Anyway see if this helps you any.

Please Login or Register  to view this content.

[Mailbags]

Thanks, JapanDave, your time in Japan has not been wasted. It works a treat.