I am having a hell of a time with this. If there's a better way around it, feel free to let me know.

I have an Excel sheet with a list of dates, among other things in it. I need to get the date in the following format

MMDDYYYY


BUT! I need to have it appear exactly like that in as text for another formula. The other formula is basically this:

=CONCATENATE(";",A1,"=",D1,E1,"0000?")

Where A1 is a number and D1 and E1 are two dates.

It SHOULD look like this:
;17052200571810=09152003091520040000?

but it actually looks like this:
;17052200571810=915200391520040000?

The problem is, the way excel seems to work is that, unless a cell is explilcity formatted to do so, any of the date functions don't use leading zeros. I don't know of any way to use cell formatting to control something like that with multiple items in the cell. Is there any way to format smaller parts in a cell? Or something? This is driving me crazy.

I've even tried making columns for the month, day, and year separately, formatting them with leading zeros, and then concatenating the values. Fucking excel gets rid of the formatting. What am I to do?

10/27/2005 1:18:56 PM

can you give me a sample one row? all the data and which columns they're in?

10/27/2005 1:23:10 PM

i kinda know what you are talking about and i have yet to figure out a way around that....i have had numerous problems trying to add data to a cell, leading in w/ a 0 and excell will not let you as far as i know of...or at least, i cant figure out how to get around that

10/27/2005 1:26:25 PM

21070510001 Wolf Andree 07022005 07022006

That's the first row in the database. This is information that's being encoded in a magstripe card.

The numbers would be cells A1, B1, C1, D1, and E1. B1 and C1 aren't used in this particular application. Also keep in mind that, while the dates in the table appear as I want them to, that is due to the formatting of the cells which goes away any time you put that data somewhere else (it's actually an excel date, so the information is just the number of days since 01/01/1900).

10/27/2005 1:31:47 PM

OK...well I formated the columns you have with the dates to be 'TEXT'.....that works for me....but if you already have a shit load of columns then going through them and reentering the zeros may be an issue...if that's the case, then let me know

10/27/2005 1:52:20 PM

you need to either format the date columns as text or you can create a custom date format that contains leading zeros. creating a custom date format is described here:

http://www.ozgrid.com/Excel/CustomFormats.htm

you want to use mmddyyyy as your date format.

10/27/2005 2:09:12 PM

Unfortunately, like I said, the formatting of a cell isn't preserved when it's used in a formula.

The actual data is, in this instance, is as follows

21070510001 Wolf Andree 38535 38900

The two numbers on the right are the actual values, sans all formatting, in the cells. The information pasted above had them formatted as MMDDYYYY.

10/27/2005 2:21:31 PM

spooky.......send me an excel file with a few rows of data and then send me a plain text file with how, and it what columns, you want the data to be.
lauren@thespinzone.com

10/27/2005 2:28:03 PM

oh i get it -- the formatting just changes how the data *looks* in the GUI. it doesn't change how the data is actually typed into the cell. hmmmmm

how do 38535 and 38900 get interpreted as dates? number of days since 1970 or something like that?

[Edited on October 27, 2005 at 2:31 PM. Reason : asdf]

10/27/2005 2:29:39 PM

Email sent. Thanks a lot for helping.

^ Yeah, number of days since 01/01/1900 I think.

10/27/2005 2:39:08 PM

this can all be done pretty easy with a macro in vb, if you wanted to go that route

10/27/2005 2:46:49 PM