Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Date format

  1. #1

    Date format



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hai. This is Hafeez. Iam the new beginner and new comer in this forum. I need help regarding the date format. User enters the value in a cell as an example 26112015 and the cell should show the value as 26/11/2015 or 26-11-2015 in date format.Please help me.

  2. #2
    Acolyte Weazel's Avatar
    Join Date
    Jul 2014
    Location
    Florida
    Posts
    26
    Articles
    0
    Excel Version
    2016
    maybe something like...

    =0+TEXT(A2,"00-00-0000")

    formatted as a date

  3. #3
    Sorry, this is not working. Actually user enters the value in a cell for example in A2 as 27112015 and in the same cell A2 the value should look like this 24/11/2015 or 27-11-2015 immediately after the focus is lost from that cell. Is there any way in VBA or else .Please help me in this regard.

  4. #4
    Acolyte Weazel's Avatar
    Join Date
    Jul 2014
    Location
    Florida
    Posts
    26
    Articles
    0
    Excel Version
    2016
    sorry, can't help you with VBA but you could custom format the cell as 00\/00\/0000 which will make it appear in the format you want but will still essentially be recognized by excel as a number and not an actual date

  5. #5
    Thanks a lot for the reply. Yes as you have mentioned it appears in the custom format but still it recognized as number. Can any one can help me using VBA or any other technique.

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013

    After entered general number formatting to Date automatically

    Quote Originally Posted by Hafeez View Post
    Can any one can help me using VBA or any other technique.
    Try see my attachment if can help you, solution with VBA.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #7
    26112015 =date(right(a1,4),left(right(a1,6),2),left(a1,2))

  8. #8
    Thank you very much the reply in an attachment. But the entry is being recognized as number. Is it not possible to be recognized as date. Could you please help me.

  9. #9
    Is it essential to convert to a date in the same cell? if not you could reference the cell that the number entry is being made, in another cell that has converted it to a date for later reference in your spreasheed.
    If you want to add the / for visual affect only, right click on the data entry cell>format cells>custom put in Type> 00"/"00"/"0000

  10. #10
    Yes, I want the same cell to be as date. If any error in input such as the month should not exceed 12 and date should not exceed 31 like that and the same should be reflected that the entry you have made is wrong.

Page 1 of 2 1 2 LastLast

Posting Permissions

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