date as integer

[lior03]

hello
the aim of the following code is to present a date number in comment while presenting the date in cell.
[VBA]
Dim cm As Comment
Dim c As Range
For Each c In selection.Cells
c.clearcomments
If Not IsEmpty(c) And IsDate(c) Then
With c.AddComment
.Text Text:="day number is:" & c.Value
.Shape.TextFrame.AutoSize = True
End With
End If
Next c

[/VBA]
is it possible to present information about a cell's content in a comment using a differnt number format?.
of instance - 21\11\2036 is day number 50000.

thanks

[Norie]

Is the date in the cell formatted as a date?

If it is then use c.Text rather than c.Value.

That should return the cell contents as you have it formatted.

[Jacob Hilderbrand]

You can format the number:

[vba]
.Text Text:="day number is: " & Format(c.Value,"dd\mm\yyyy")
[/vba]

[Zack Barresse]

[vba]"dd\mm\yyyy"[/vba]

??

Wouldn't it be "dd/mm/yyyy"??

[Jacob Hilderbrand]

I just went of the format of the date in the original post. If you want to get technical it should be mm/dd/yyyy anyway.

[Zack Barresse]

.. unless you're on the other side of the pond.. which moshe is..

[Insomniac]

Perhaps to consider:

.Value (is dependant on your windows reginal shortdate)
.Text (reflects the .value status)

.Value2 (returns the true date serial number)

My point being if the cell is really formatted for a date then .Value2 will give you the exact serial number. Clng.Value should also work.