PDA

View Full Version : date as integer



lior03
04-16-2006, 11:37 AM
hello
the aim of the following code is to present a date number in comment while presenting the date in cell.

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


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
04-16-2006, 12:04 PM
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
04-16-2006, 12:13 PM
You can format the number:


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

Zack Barresse
04-16-2006, 12:25 PM
"dd\mm\yyyy"
??

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

Jacob Hilderbrand
04-16-2006, 12:30 PM
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. :p

Zack Barresse
04-16-2006, 12:31 PM
.. unless you're on the other side of the pond.. which moshe is.. ;)

Insomniac
04-16-2006, 12:58 PM
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.