Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
count between start date and end date | Excel Discussion (Misc queries) | |||
The way the date displays in columns | New Users to Excel | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions |