Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Omit '/' in date (Excel97/SR2)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Omit '/' in date (Excel97/SR2)

    If your date is in A1, then <pre><big> =TEXT(SUBSTITUTE(A1,"/",""),"00000000")</big></pre>

    should work.

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Omit '/' in date (Excel97/SR2)

    That worked!
    Thanks!!!
    LJM

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Omit '/' in date (Excel97/SR2)

    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
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •