LinkBack URL
About LinkBacks
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!!!
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!!!
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
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!!
Think you will need VBA code then
VBA Noob
Do you have an idea of how to do that?
Sorry no.
Think you will have to repost to that section for a answer.
VBA Noob
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
>
>
To what do I assign this macro to?
Originally Posted by CLR
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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks