Printing Date in column

[parth007]

Hello Members,

This seems to be very simple for all you experts.. but me finding it difficult...

I Have a column named as Date which will have current date..
so in VBA code i wrote...

Now()

But the user needs different format..
"MMDDYY" format of current date
Example - The output file should have date value = 230115
Please suggest

 

[Luke M]

You mention VBA, but used a regular formula nomenclature, so I'm a little confused. Nevertheless, how to do it:
In column of interest, put formula:
=TODAY()
Then, format the cells to desired date format, or use a custom date format of
MMDDYY

 

[Somendra Misra]

Hi,

Suppose you want in A1, try below code:

With Range("A1")

.Value = Date
.NumberFormat = "MMDDYY"
End With

Regards,

 

[parth007]

hiLuke.. I uesd

Format(Now(), "DD-MM-YY")

So output is 23-01-15

Now i have to exclude "-"

 

[parth007]

Somendra i used your code.. but i got 1/23/2015

I need output as 230115

 

[Somendra Misra]

See this:



Regards,

 

[parth007]

Actually i converted the column to text & below code worked

Sheets("Resultant").Range("I2:I5000").Value = Format(Now(), "DD-MM-YY")
 
 ActiveSheet.Range("I2:I5000").NumberFormat = "@"
 
 Dim rng As Range, cell As Range
  Set rng = ActiveSheet.Range("I2:I5000")
  For Each cell In rng
  cell = WorksheetFunction.Substitute(cell, "-", "")
  Next

 

[Somendra Misra]

Sheets("Resultant").Range("I2:I5000").Value = Format(Now(), "DD-MM-YY")
Dim rng As Range, cell As Range
  Set rng = ActiveSheet.Range("I2:I5000")
  For Each cell In rng
  cell = WorksheetFunction.Substitute(cell, "-", "")
  Next

This code worked

Using this code will may give you the required format but you will lose the date with a number. I would suggest you to keep the date intact as that will help you do lot of things, like filter on month basis.

Regards,

 

[parth007]

thanks it worked