Omit ‘/’ in date (Excel97/SR2)

Topic

New Reply

We have downloaded a check list from our payroll service (ADP) and the check dates download as text and of course include the “/” in them. In order to send this file to our bank to perform the bank reconciliation process the date needs to be converted into 8 characters without the “/” in it. For example, 7/26/2002 is what is downloaded from ADP (and is in text format) and it needs to look like this 07262002. Of course to complicate things, the zero at the beginning needs to be added for the first 9 months of the year, and doesn’t need to be added for the last three months of the year. Anybody have any ideas on how a one-step formula can convert this?
Thanks!
LJM

Reply | Quote

Replies

If your date is in A1, then

	=TEXT(SUBSTITUTE(A1,"/",""),"00000000")

should work.

Andrew C

Reply | Quote

That worked!
Thanks!!!
LJM

Reply | Quote

Hi,

If your output is going from Excel to a text file, you don’t even need a formula. Instead, you could use a custom format for the dates, in the form of:
mmddyyyy

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote