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.

 

[Norie]

Put 1 in an empty cell, copy it, select the range with the dates, then Paste>Paste Special... and select Subtract from the Operation section.

That should subtract a day.

For the formatting again select the dates (note they might be in decimal format after the subtraction but that shouldn't matter) and then Format>Custom format...:

MMDDYYYY

 

[Dave Patton]

You state "requires that the date column is in the format DDMMYYYY" but the rest of your explanation uses MMDDYYYY.

- subtract one from date
copy a cell that has 1 in it
move to cell or cells with date(s)
edit pastespecial subtract

- use custom format for required date

- convert to value

 

[1Coolhand]

Thanks for the reply.

I put a 1 in a blank cell, copied that, selected the date column, chose paste special, went to operations and chose subtract. When I did that, it converted all of the dates from 6/2/2011 to 40695. Not sure why it did that?

 

[1Coolhand]

To Dave,

Sorry, it needs to be in MMDDYYYY format.

Thanks for the reply.

 

[julia55]

You can use this formula to subtract 1 day and reformat the date all at the same time.

=TEXT(A2-1,"MMDDYYYY")

 

[1Coolhand]

Thanks for the help!

Disregard the 04965 post. I didn't follow all of the directions about converting the column to custom format MMDDYYYY.

One other question. Right now, the spreadsheet has about 7500 rows. This will grow I am sure. Since I only pasted that special to a certain amount of rows, what happens if the extract contains about 8000 rows. I am automating all of this so it doesn't require human interaction.

Thanks again!!!!!

 

[Norie]

You can just do the paste special for however many rows are needed.

If you have code that's currently doing it but the range is hard coded it's quite easy to change it to take into account the no of rows of data.

 

[1Coolhand]

When I do the paste special (of the 1 and subtract), how can I tell it to only do rows where data exists? If I do the paste special, the rows that have data in it are right, but it fills the rest of the empty rows at the end of the spreadsheet with -1.

 

[1Coolhand]

Also, there is no hardcoding for the number of rows as the total number of rows in the extract file I am using fluctuates daily.

Thanks again for any help.