Printable View
I have following VBA: -
[VBA]
Dim DateVal As String
DateVal = Format(Date, "dd-mm-yyyy")
[/VBA]
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.
Maybe ...
[VBA]
Columns("D:D").NumberFormatLocal = "dd-mm-yyyy"
[/VBA]
Paul
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.Quote:
Originally Posted by GTO
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?
If you want Excel to use the Regional Settings for date formating,
[vba]
.NumberFormatLocal = "m/d/yyyy"
[/vba]
is the way to go
Paul
Quote:
Originally Posted by GTO
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?
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:
[VBA]
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
[/VBA]
5. Or you can use WRS
[VBA]
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
[/VBA]
Paul
Hi,
I hope this will solve your problem : -
[VBA]
Private Sub Workbook_Open()
Dim LValue As String
LValue = Format(Date, "Short Date")
Range("A1").NumberFormat = "@"
Range("A1").Value = LValue
End Sub
[/VBA]
You have to first ensure that cell is of same data type where your data will go.
Thanks,
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.
[VBA]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[/VBA]
On the other hand, this causes October 5, 2011 to be put in A1 and show 05-10-2011 as desired.
[VBA]With Range("A1")
.NumberFormat = "dd-mm-yyyy"
.Value = Date: Rem October 5, 2011
MsgBox .Text: Rem 05-10-2011
End With[/VBA]
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.
I also wanted to know what this code will do: -
[vba]
Cells(1,1).NumberFormat = "[$-409]d/mmm/yy;@"
[/vba]
and why this code was used before formatting: -
[vba]
[$-409]
[/vba]
Is there any special meaning to using following at end of formatting: -
[VBA]@[/VBA]
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.