Date format

Hafeez

New member
Joined
Nov 26, 2015
Messages
6
Reaction score
0
Points
0
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.
 
maybe something like...

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

formatted as a date
 
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.
 
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
 
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.
 
After entered general number formatting to Date automatically

Can any one can help me using VBA or any other technique.
Try see my attachment if can help you, solution with VBA.
 

Attachments

  • hafeez-navic.xlsm
    14.7 KB · Views: 4
26112015=date(right(a1,4),left(right(a1,6),2),left(a1,2))
 
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.
 
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
 
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.
 
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.
I do not know why you yourself complicates life?
Is it a problem that you or users write a regular date?

OK
If you must import numbers from a list, then you can "try" do the following

Option1
Copy the list of numbers in a column for example. H column (You can hide column H)
Into column A set formula, which will number from column H converted to a real date

Option2
If it does not fit, here's an example with the VBA as a guideline for solving problems. (Note: I am not a VBA programmer, might have errors)
To set the selection active cell if some are entering data in other cells. After press the Enter key, VBA selecting Last Cell in Column A. But I do not want it. However I do not know how to solve
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

      Application.CutCopyMode = True
      Application.ScreenUpdating = True
    
    Range("A2:A25").Select
    Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
      
      Application.CutCopyMode = False
      Application.ScreenUpdating = False
'I don't know how to solve this problem after press enter key if you entering data in to another columns
             'ActiveCell.Select '????
             'ActiveCell.Offset(1, 0).Select '????
             'ActiveCell.Cell(Rows.Count, 1).End(xlUp).Offset(1, 0) '????
              ActiveCell.End(xlDown).Offset(1, 0).Select 'selecting first empty cell in column

End Sub
Also, try search for Data Validation restricting

See attachment file
 

Attachments

  • hafeez-navic2.xlsm
    47.4 KB · Views: 2
You can simply change the format of the cell in the general tab :)
 
Back
Top