• Omit ‘/’ in date (Excel97/SR2)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Omit ‘/’ in date (Excel97/SR2)

    Author
    Topic
    WSLJM
    AskWoody Lounger
    July 29, 2002 at 6:00 pm #374245

    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
    Viewing 1 reply thread
    Author
    Replies
    • WSAndrew Cronnolly
      AskWoody Lounger
      July 29, 2002 at 6:13 pm #604826

      If your date is in A1, then 

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

      should work.

      Andrew C

      Reply | Quote
    • macropod
      AskWoody_MVP
      July 30, 2002 at 10:31 pm #605073

      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
    Viewing 1 reply thread
    Reply To: Omit ‘/’ in date (Excel97/SR2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.