+ Reply to Thread
Results 1 to 3 of 3

Custom number format to allow skipping the key punch of backslash in a date

  1. #1
    Registered User
    Join Date
    11-17-2009
    Location
    Grand Rapids, MI
    MS-Off Ver
    Office 2010
    Posts
    7

    Custom number format to allow skipping the key punch of backslash in a date

    I want to allow our data entry clerks to avoid entering backslashes when entering dates. I can use custom format 00\/00\/0000 to allow 04052016 to display 04/05/2016. And I understand that it not the serial date in the cell.

    Question is can I also avoid entering first 2 digits of year? So if one enters 040516 excel would display 04/05/2016.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Custom number format to allow skipping the key punch of backslash in a date

    I don't think a custom format will be able to accomodate that.

    Honestly, your efforts will be far more effectively spent instructing the data entry clerks to correctly enter dates with the /.
    a) You don't have to spend time and effort later converting the entries into real dates after the fact.
    b) It will accept 2 digit year entries

    Would it be easier for them if they could use period as the date separator ? 04.15.16 ?

    Or perhaps have the date entered in 3 different cells, one for Day, One for Month, and One for Year
    This way they could press the Tab key (with the left hand) between each part of the date.
    Then you can combine them with a =DATE(year,month,day) formula.
    Last edited by Jonmo1; 04-12-2016 at 10:21 AM.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,919

    Re: Custom number format to allow skipping the key punch of backslash in a date

    Another option would be to allow them to type a 6-digit number into a cell that is formatted as Text, then have a formula in another cell to convert that to a date for proper display. The 6-digit cell must be formatted as Text to allow a leading zero.

    =DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2))

    If one enters 040516 Excel would display 04/05/2016 in the second cell (if you use a mm/dd/yyyy custom format).

    This could also be done with a macro, thus converting the date in the same cell where it is typed (see attachment). This example will convert dates only when typed into column A.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 04-12-2016 at 11:02 AM. Reason: added blue text

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need formula or code to convert number(s) from custom format to number format
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-12-2015, 07:44 PM
  2. Clock in multiple punch in/punch out times Formula
    By Schnizzle74 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2014, 06:15 PM
  3. Analysis of Punch Clock Data in new format
    By samfarrugia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2012, 08:08 AM
  4. Custom format number and date together
    By blueMoon in forum Excel General
    Replies: 2
    Last Post: 01-30-2007, 08:25 PM
  5. how do I add phone number format as a permanent custom format?
    By frustratedagain in forum Excel General
    Replies: 3
    Last Post: 02-03-2006, 11:52 PM
  6. [SOLVED] Format a cell with a custom number format
    By Armor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-29-2006, 07:30 PM
  7. [SOLVED] Custom number format always defaults last number to 0.
    By scubadave in forum Excel General
    Replies: 2
    Last Post: 06-15-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1