View Full Version : Solved: Date format
ianswer
10-04-2011, 01:35 PM
I have following VBA: -
Dim DateVal As String
DateVal = Format(Date, "dd-mm-yyyy")
Regional Settings - Short Date - was already changed to dd-mm-yyyy
I wanted to ensure that Col D all cells from 2nd row onwards to have same format as aforesaid. How can I do?
Hi there,
At least as posed, select all the cells from row 2 and downward in Col D, right-click and choose format cells... Then select the number tab and custom in the category box. If the format you want is not listed, build it there.
Paul_Hossler
10-04-2011, 06:42 PM
Maybe ...
Columns("D:D").NumberFormatLocal = "dd-mm-yyyy"
Paul
ianswer
10-05-2011, 01:02 AM
Hi there,
At least as posed, select all the cells from row 2 and downward in Col D, right-click and choose format cells... Then select the number tab and custom in the category box. If the format you want is not listed, build it there.
That's good idea. I can record Marco using your suggestion. But, I don't know why my system was not able to format Date.
Did you try Paul's suggestion?
Could you give a couple of examples of what exactly is being entered and how, where Excel does not coerce it?
Paul_Hossler
10-05-2011, 10:33 AM
If you want Excel to use the Regional Settings for date formating,
.NumberFormatLocal = "m/d/yyyy"
is the way to go
Paul
ianswer
10-05-2011, 12:02 PM
Did you try Paul's suggestion?
Could you give a couple of examples of what exactly is being entered and how, where Excel does not coerce it?
Thanks Gentlemen,
Please refer to attachment.
Please view that LValue contains a string in format of DD-MM-YYYY
But, it passes that string to cell in format of MM-DD-YYYY
I don't know why?
Paul_Hossler
10-05-2011, 01:17 PM
1. It's not clear to me why you're using the LValue string at all
2. Once Excel has a Date in a cell, there after any numerical value gets treated as a date
3. LValue = Format (Date, "dd-mm-yyyy") has nothing to do with WRS.
4. You can 'override' WRS:
Option Explicit
Private Sub Workbook_Open()
Range("A:A").NumberFormatLocal = "dd-mm-yyyy"
Range("A1").Value = Now
Range("A2").Value = Now + 1
Range("A3").Value = Now + 3
Range("A4").Value = Now + 5
Range("A5").Value = Now + 7
End Sub
5. Or you can use WRS
Option Explicit
Private Sub Workbook_Open()
Range("A:A").NumberFormatLocal = "m/d/yyyy"
Range("A1").Value = Now
Range("A2").Value = Now + 1
Range("A3").Value = Now + 3
Range("A4").Value = Now + 5
Range("A5").Value = Now + 7
End Sub
Paul
justdriving
10-05-2011, 01:29 PM
Hi,
I hope this will solve your problem : -
Private Sub Workbook_Open()
Dim LValue As String
LValue = Format(Date, "Short Date")
Range("A1").NumberFormat = "@"
Range("A1").Value = LValue
End Sub
You have to first ensure that cell is of same data type where your data will go.
Thanks,
mikerickson
10-05-2011, 06:14 PM
I am concerned with putting a string into a cell and expecting it to become a number (date). I always hesitate to trust automatic conversion. I can't complain about my result if I don't ask for what I want.
For example, this causes the date May 10, 2011 to be put in A1 and show as 10-05-2011, because the auto-coversion from text to date does not take the number formatting into account.
With Range("A1")
.NumberFormat = "dd-mm-yyyy"
.Value = Format(Date, "dd-mm-yyyy"): Rem Date = October 5, 2011
MsgBox .Text: Rem 10-05-2011
End With
On the other hand, this causes October 5, 2011 to be put in A1 and show 05-10-2011 as desired.
With Range("A1")
.NumberFormat = "dd-mm-yyyy"
.Value = Date: Rem October 5, 2011
MsgBox .Text: Rem 05-10-2011
End With
ianswer
10-06-2011, 12:17 PM
What does following do: -
cells(1,1).NumberFormat = "@"
If you test that on a new/blank/throwaway wb, you will see that it changes the cell's numberformat to Text.
ianswer
10-07-2011, 01:31 PM
I also wanted to know what this code will do: -
Cells(1,1).NumberFormat = "[$-409]d/mmm/yy;@"
and why this code was used before formatting: -
[$-409]
Is there any special meaning to using following at end of formatting: -
@
ianswer
10-07-2011, 01:41 PM
I got it...
The [$-409] is a locale code, given in hexadecimal. Prefixing a date with a certain locale code determines what is shown when you use the various date time format codes.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.