Comment Displays date
 

While hovering over a cell which contains the date in this format:
dd/mm/yyyy, i want the comment to display the date in the format:
"Month" "Day"'th, "YYYY" such as June 12th, 2006.

Thank you!!!


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

You can format the cell as

mmmm dd, yyyy

or if you do need the Ordinal numbers then enter your date in A2 and
put the below in B2 and drag down


=TEXT(A2,"mmmm")&" "&DAY(A2)&IF(INT(MOD(DAY(A2),100)/10)=1, "th",
IF(MOD(DAY(A2),10)=1, "st", IF(MOD(DAY(A2),10)=2,"nd",
IF(MOD(DAY(A2),10)=3, "rd","th"))))&" ,"&TEXT(A2,"YYYY")

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

Thank you. I *do* have to keep the format in ordinal form.


*HOWEVER*, i asked for the date to be displayed in the *comment*, not
in another cell.

Thank you!!


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

Think you will need VBA code then

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

Do you have an idea of how to do that?


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

Sorry no.

Think you will have to repost to that section for a answer.

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

This is quite crude, but seems to get the job done...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' This macro, placed in a WorkSheet module will automatically
' copy a cell's value over to it's comment box, formatted as Month Day (th),
Year
' like June 1st, 2006, June 2nd, 2006, etc etc once the cell has been
activated.
On Error Resume Next
If Day(ActiveCell.Value) = 1 Or Day(ActiveCell.Value) = 21 Or
Day(ActiveCell.Value) = 31 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "st, "
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 2 Or Day(ActiveCell.Value) = 22 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "nd, "
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 3 Or Day(ActiveCell.Value) = 23 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "rd, "
& Format(ActiveCell.Value, "yyyy")
Else
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "th, "
& Format(ActiveCell.Value, "yyyy")

End If
End If
End If
End Sub


hth
Vaya con Dios,
Chuck, CABGx3



"aposatsk" wrote:


Do you have an idea of how to do that?


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

To what do I assign this macro to?

CLR Wrote:
This is quite crude, but seems to get the job done...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' This macro, placed in a WorkSheet module will automatically
' copy a cell's value over to it's comment box, formatted as Month Day
(th),
Year
' like June 1st, 2006, June 2nd, 2006, etc etc once the cell has been
activated.
On Error Resume Next
If Day(ActiveCell.Value) = 1 Or Day(ActiveCell.Value) = 21 Or
Day(ActiveCell.Value) = 31 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "st,
"
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 2 Or Day(ActiveCell.Value) = 22 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "nd,
"
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 3 Or Day(ActiveCell.Value) = 23 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "rd,
"
& Format(ActiveCell.Value, "yyyy")
Else
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "th,
"
& Format(ActiveCell.Value, "yyyy")

End If
End If
End If
End Sub


hth
Vaya con Dios,
Chuck, CABGx3



"aposatsk" wrote:


Do you have an idea of how to do that?


--
aposatsk

------------------------------------------------------------------------
aposatsk's Profile:
http://www.excelforum.com/member.php...o&userid=36709
View this thread:
http://www.excelforum.com/showthread...hreadid=567176




--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=567176

Comment Displays date
 

You don't really have to "assign" this macro to anything, per se........it's
a Worksheet Change Event macro and actuates automatically upon it's
conditions being met......to install it

Of course, do this first on a copy of your file, for safety......

First Copy the macro out of the post, then Right-click on the Sheet Tab of
the Sheet you wish it to operate on....then choose ViewCode.....a new window
will pop up, this is the VBA Editor, or VBE. Move the mouse over the small
windows at the top of the big window on the right until the helper prompt
"Object" shows up, and click the arrow on that window and select
"Worksheet"...........then move to the large window below and Paste the
macro in there..........and that's it, it will run automatically from there
on..........the only danger with this procedure is that sometimes the posting
of a macro will cause "wordwrap" and some of one or more of the lines of code
will drop down to the next line and will not function correctly......usually
these lines will appear in RED, so just carefully backspace the front end of
those lines until they back up to the line above..........that should do
it.......

hth
Vaya con Dios,
Chuck, CABGx3




"aposatsk" wrote:


To what do I assign this macro to?

CLR Wrote:
This is quite crude, but seems to get the job done...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' This macro, placed in a WorkSheet module will automatically
' copy a cell's value over to it's comment box, formatted as Month Day
(th),
Year
' like June 1st, 2006, June 2nd, 2006, etc etc once the cell has been
activated.
On Error Resume Next
If Day(ActiveCell.Value) = 1 Or Day(ActiveCell.Value) = 21 Or
Day(ActiveCell.Value) = 31 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "st,
"
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 2 Or Day(ActiveCell.Value) = 22 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "nd,
"
& Format(ActiveCell.Value, "yyyy")
Else
If Day(ActiveCell.Value) = 3 Or Day(ActiveCell.Value) = 23 Then
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "rd,
"
& Format(ActiveCell.Value, "yyyy")
Else
ActiveCell.Comment.Text Text:=Format(ActiveCell.Value, "mmmm d") & "th,
"
& Format(ActiveCell.Value, "yyyy")

End If
End If
End If
End Sub


hth
Vaya con Dios,
Chuck, CABGx3



"aposatsk" wrote:


Do you have an idea of how to do that?


--
aposatsk

------------------------------------------------------------------------
aposatsk's Profile:
http://www.excelforum.com/member.php...o&userid=36709
View this thread:
http://www.excelforum.com/showthread...hreadid=567176




--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=567176