Excel Date Issue

[1Coolhand]

Hello all,

I have an issue that I am trying to resolve. I have an excel spreadsheet that we save into .csv format to upload to a 3rd party system. This system requires that the date column is in the format DDMMYYYY. When I get the extract, there are about 7500 columns with the date listed (ex 6/2/2011) The column is formatted as General.

I need to be able to do the following:

1. Convert this column to MMDDYYYY format (06022011) with the leading zero for single digit months and single digit days.

2. I need to subtract 1 day from the result (ex. 06022011 needs to go to 06012011 or for the first day of the month 06012011 needs to go to 05312011)

I am not a large VBA guru so any help would be appreciated.

 

[CharlesChuckieCharles]

TRY THIS

Public Sub dATEIT()
Dim c As Range
 
     On Error Resume Next
     For Each c In Selection
 
        c.Value = "'" & Format(c.Value - 1, "mmddyyyy")
 
 
     Next c
 
End Sub

put it in a module

then select the date column

and run

BACK UP FIRST BECAUSE THIS CHANGES THE DATA TO TEXT !!!!

 

[Norie]

Can you post the code you have?

Also what do you mean by 'only pasted that special to a certain amount of rows, what happens if the extract contains about 8000 rows'?

Does that not mean the code you have doesn't cater for the changing amount of rows?