Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert time to text
G'day Everyone
I need a work around for the following please: Case Target.Value = DateSerial(2008, 9, 1) And Target.Value < DateSerial(2008, 10, 1) ActiveCell.Offset(0, 1).Select Selection.Value = "SEP" ' Column G, [no problem here.] ActiveCell.Offset(0, 1).Select Selection.Value = Target.Value 'Column H, in this case the cell is formatted (dddd) which = Tue. [no probs here either.] ActiveCell.Copy 'I then need to copy Column H result [Tue] to Column L ActiveCell.Offset(0, 4).Select Selection.PasteSpecial xlPasteValues 'Convert result, which in this case is 39707 to display [Tue] as TEXT The reasoning behind this is that I have an array lookup, it doesn't work unless I use the text equivalent, as it reads Column H as a date/time and comes up as #N/A, I can then change the MATCH reference accordingly. Look forward to your thoughts & suggestions TIA Mark. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert time to text
instead of copy/paste special, try:
Activecell.Offset(0,4).Value = Activecell.Text but if this is for an array lookup, why not use the Text function in your array lookup formula? for example Match("Tue", Text(A1:A100, "ddd"), 0) "NoodNutt" wrote: G'day Everyone I need a work around for the following please: Case Target.Value = DateSerial(2008, 9, 1) And Target.Value < DateSerial(2008, 10, 1) ActiveCell.Offset(0, 1).Select Selection.Value = "SEP" ' Column G, [no problem here.] ActiveCell.Offset(0, 1).Select Selection.Value = Target.Value 'Column H, in this case the cell is formatted (dddd) which = Tue. [no probs here either.] ActiveCell.Copy 'I then need to copy Column H result [Tue] to Column L ActiveCell.Offset(0, 4).Select Selection.PasteSpecial xlPasteValues 'Convert result, which in this case is 39707 to display [Tue] as TEXT The reasoning behind this is that I have an array lookup, it doesn't work unless I use the text equivalent, as it reads Column H as a date/time and comes up as #N/A, I can then change the MATCH reference accordingly. Look forward to your thoughts & suggestions TIA Mark. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert time to text
Hi,
Here are some other suggestions regarding your code: Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Application.Intersect(Target, [A1:A50]) If Not isect Is Nothing Then With Target Select Case Target Case DateSerial(2008, 9, 1) To DateSerial(2008, 9, 30) .Offset(0, 1) = "SEP" .Offset(0, 2) = Target .Offset(0, 6) = Format(Target, "DDD") 'Case..... End Select End With End If End Sub Hope this gives you some ideas. Cheers, Shane Devenshire "NoodNutt" wrote in message ... G'day Everyone I need a work around for the following please: Case Target.Value = DateSerial(2008, 9, 1) And Target.Value < DateSerial(2008, 10, 1) ActiveCell.Offset(0, 1).Select Selection.Value = "SEP" ' Column G, [no problem here.] ActiveCell.Offset(0, 1).Select Selection.Value = Target.Value 'Column H, in this case the cell is formatted (dddd) which = Tue. [no probs here either.] ActiveCell.Copy 'I then need to copy Column H result [Tue] to Column L ActiveCell.Offset(0, 4).Select Selection.PasteSpecial xlPasteValues 'Convert result, which in this case is 39707 to display [Tue] as TEXT The reasoning behind this is that I have an array lookup, it doesn't work unless I use the text equivalent, as it reads Column H as a date/time and comes up as #N/A, I can then change the MATCH reference accordingly. Look forward to your thoughts & suggestions TIA Mark. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert time to text
Thx heaps guy's
Workbook works great. Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Time to a Text | Excel Worksheet Functions | |||
Convert Text Time to Excel Time | Excel Discussion (Misc queries) | |||
Convert Text to time value | Excel Discussion (Misc queries) | |||
Text to time convert | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions |