+ Reply to Thread
Results 1 to 3 of 3

Dates in a column to appear in a specific format.

  1. #1
    Registered User
    Join Date
    03-06-2020
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    81

    Dates in a column to appear in a specific format.

    My target range is "E4:E500" (Heading is date) . I want all the dates to be format dd-mmm (eg :- 5-feb , 14-mar etc ). If i enter only day then add current ongoing month to it (eg:- if 5 is entered then output should be 5-mar). If 5-4-2020 is entered then output should be 5-apr. Also i want to restrict the user . Only 1 to 31 numbers or a date shld be allowed else display warning.
    Last edited by thebeastslayer; 03-06-2020 at 03:23 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,713

    Re: Dates in a column to appear in a specific format.

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Fri 06 Mar 2020) is actually 43896

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    What you want is entirely do-able, excel tries to complete a date for you anyway, as long as you start entering it the right way. Entering 5/5 {enter} will produce 5-May by default, but you can also preset the format of those cells to only display day/month

    To restrict entry to 1-31 IS technically possible - using data validation, but, because of how I explained how dates worked, excel will not actually "see" the days
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-06-2020
    Location
    INDIA
    MS-Off Ver
    2007
    Posts
    81

    Re: Dates in a column to appear in a specific format.

    Thanks for info. Can u give me code that if 1-31 number is entered in the range then add 31 to it then change format to dd-mmm . It should do the trick . It will automatically convert jan to feb. I will alter the "add 31" according to the month i desire .

+ 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. [SOLVED] Sum between dates for a specific column and specific time frame
    By excel_tmd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2019, 03:10 PM
  2. [SOLVED] VBA Code to format a column to a specific number format
    By TreeLife in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-24-2018, 09:26 PM
  3. [SOLVED] Calculate TAT between 2 dates and show only working hours in specific format
    By allienzaddicts in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-16-2018, 11:03 PM
  4. vba code to format a column to a specific format (SSSS X1111111)
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2017, 03:16 PM
  5. Not so excel savvy- Need to conditional format specific dates in a range.
    By Seasons23 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 03-07-2014, 08:18 AM
  6. Replies: 2
    Last Post: 12-17-2013, 06:30 AM
  7. Replies: 6
    Last Post: 07-01-2012, 05:10 PM

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