[RESOLVED] American Date Format - vba

[gphillips]

Hi Guys,

Need a way to programatically/dyanmically shown today date in american format:-
i.e 2/28/2006


Must be formated as per bold above?


Can anyone advise?

Boris

[gphillips]

Any anyideas chaps ? I am trying to do this for excel vba.

Boris

[thelocaluk]

Me.datebox.Value = Format(Now(), "mm/dd/yyyy")

[gphillips]

Thanks for trying to help but its doesn't work basiaclly I'm trying to offset the date against column A which already contains random data. It doesn't like it for some reason, could you help me debug.

Sub Dates()

Dim filelength As Long

filelength = ActiveSheet.UsedRange.Rows.Count

Range("a1").Activate 'Activates a2
For i = 1 To filelength

ActiveCell.Offset(0, 1).formula = Me.datebox.Value = Format(Now(), "mm/dd/yyyy")
ActiveCell.Offset(1, 0).Activate
Next i
End Sub

Thanks Boris.

[RobDog888]

You cant have 2 equal signs on the same line.

ActiveCell.Offset(0, 1).formula = Me.datebox.Value = Format(Now(), "mm/dd/yyyy")


Whats the offset needing to be? the cell value plus a date value?

[gphillips]

Basically I have random data in column a (any length).

Just need the american date format in column B offseted against the used range in column A.

It need to fixed in column B and dynamic (live) each time the programe is run:-

i.e 28/2/2006


Must be exactly that format , no times etc.


Thanks Very much, hope you can help


Best Regards,

Boris

[RobDog888]

Something like this but you may want to trap for the appropriate range first so it doesnt try to format invalid data from another column.
= Format(ActiveCell, "mm/dd/yyyy")

[gphillips]

Hi M8,

Nearly there I suppose but not quite the desired result, this may explain what I need , column A contains "BOB" (OR ANY RANDOM DATA not related). Column contains today date live (american style) as above.


This is the output when I run the program:-

cOLUMNA cOLUM B
BOB 12/31/1899
BOB 01/01/00
BOB 02/01/00
BOB 02/01/00
BOB 02/01/00
BOB 02/01/00
BOB 02/01/00
BOB 02/01/00
BOB 02/01/00

This is what I have:



Sub Dates()

Dim filelength As Long

filelength = ActiveSheet.UsedRange.Rows.Count
Range("a1").Activate 'Activates a2
For i = 1 To filelength
ActiveCell.Offset(0, 1).formula = Format(ActiveCell, "mm/dd/yyyy")
ActiveCell.Offset(1, 0).Activate
Next i
End Sub

[RobDog888]

I dontsee where your changing the cell in your loop. It needs either to be referenced directly or each cell needs activating so the ActiveCell will change. Right now its always taking from the save value.

[gphillips]

This part of code is changing the row each time.

ActiveCell.Offset(1, 0).Activate


Please bear with me , I'm a junior at coding:-

I assume you get the "just" of what I'm trying to achieve, appreciate if you help finish what I'm trying to do.

filelength = ActiveSheet.UsedRange.Rows.Count

The above part of code is needed making it it only available to the used range which is what Iwant "integligence in knowing where to stop". I assume I need to use a loop.


If you can thinking of a smarter way then great m8. Appreciate if you can add some comments where neccessary - so can understand and learn.

Look forward to your response.

Boris

[RobDog888]

Ok, lets re-think this. What is the date column (B) supposed to increment by? Increment by a day, a month, or a year?

[gphillips]

Hi it only meant to increment to today date live each time the program is run. Therefore it has to be dynamic.

so today will be:-

2/27/2006

tommorrow will be

2/28/2006


Must be exactly this format.


Hope this clarifies,
B

[RobDog888]

ActiveCell.Offset(0, 1).formula = Format(Date, "mm/dd/yyyy"):)

[gphillips]

Thanks , you truly are a genius.

You've savedme a sleeples night.

[RobDog888]

Glad someone is getting sleep as I am working on a sql db issue with only 3 hours sleep so far. :(

Thanks for the props :)

Ps, dont forget to Resolve your thread. ;)