Convert time to text

[NoodNutt]

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.

 

[JMB]

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)

 

[Shane Devenshire]

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]

Thx heaps guy's

Workbook works great.

Mark.