Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-01-2012, 08:23 AM
Bondai Bondai is offline Change format of date when using Now function in VB code Mac OS X Change format of date when using Now function in VB code Office for Mac 2011
Novice
Change format of date when using Now function in VB code
 
Join Date: Feb 2012
Posts: 3
Bondai is on a distinguished road
Exclamation 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


Reply With Quote
  #2  
Old 03-02-2012, 11:51 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Change format of date when using Now function in VB code Windows 7 32bit Change format of date when using Now function in VB code Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 03-02-2012, 05:09 PM
JBeaucaire JBeaucaire is offline Change format of date when using Now function in VB code Windows XP Change format of date when using Now function in VB code Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

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?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change format of date when using Now function in VB code VBA code to change carlleese24 Excel Programming 2 08-13-2011 09:48 AM
Change format of date when using Now function in VB code 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
Change format of date when using Now function in VB code Imported message date change to today's date promark Outlook 1 12-23-2005 07:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft