| 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| 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 | |||
Linear Mode
