#1
|
|||
|
|||
Change format of date when using Now function in VB code
Hi guys
Can anyone advise how to change the format of a date automatically inserted via using the Now function in some VB code ? Here is the code: ---------------------------- Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Intersect(Target, Range("H4:Q119")) Is Nothing Then Exit Sub Application.EnableEvents = False If Target.Interior.ColorIndex = xlNone Then Target.Interior.ColorIndex = 3 Target = "X" Range("U" & Target.Row) = "FAIL" ElseIf Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 4 Target = "P" Range("T" & Target.Row) = Now Range("U" & Target.Row) = "PASS" ElseIf Target.Interior.ColorIndex = 4 Then Target.Interior.ColorIndex = xlNone Target = "" Range("T" & Target.Row) = "" Range("U" & Target.Row) = "" End If Application.EnableEvents = True Cancel = True End Sub ---------------------------- Here is the date format I would like to use: "dd/mm/yy - hh:mm" (I have highlighted the area of code (RED) where the date needs to change format). This is bugging me totally and trying to search through the million threads about date functions is like looking for a needle in a haystack. My lack of knowledge probably hindering the accuracy of my search. Thanks Bondai |
#2
|
||||
|
||||
You need to use the Range.NumberFormat property. Something like this:
Code:
With Range("T" & target.Row) .Value = Now .NumberFormat = "dd/mm/yy - hh:mm" End With |
#3
|
|||
|
|||
Since I only see you putting some text and timestamps in column T, why don't you simply format that column for the DATE/TIME format you want. Once you've done that, putting TEXT in that cell won't cause a problem, and when a timestamp is added back in it will already be the correct format.
Or is there other code somewhere that is replacing the formatting of cells in that column? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA code to change | carlleese24 | Excel Programming | 2 | 08-13-2011 09:48 AM |
Today's Date Function | freschij | Excel | 3 | 02-11-2011 10:21 AM |
Automatic Date Function Corrupting Printer Output | Theodulf | Word | 0 | 05-28-2010 12:33 PM |
Date Function in word? | aligahk06 | Word | 1 | 04-21-2010 06:33 AM |
Imported message date change to today's date | promark | Outlook | 1 | 12-23-2005 07:21 AM |