Is it possible to type a date range like "15/01 18/01" and take a result like "15-Jan to 18/Jan".
Not based on dates from different cells but directly convert into that format.
Is it possible to type a date range like "15/01 18/01" and take a result like "15-Jan to 18/Jan".
Not based on dates from different cells but directly convert into that format.
Excel records dates as numbers. For example, the date of Jan 18, 2018 has the number 43118. You can prove that. Enter the date in a virgin cell, convert the number format to 'Number' and the number will be displayed in the cell.
Access the NumberFormat from the right-click menu > Format Cells or from the ribbon's home tab > Format > Format Cells. By default all cells are formatted as 'General' meaning Excel will guess what kind of entry you make and apply a suitable fromat. You can over-ride that by selecting a format other than 'General'.
There are lots of date formats you can select in the Format Cells dialog box - "dd mmm" is one of them - or you can create a custom format. Once that format has been applied to a cell whatever you type into it will be displayed in that format. For example, if the format is "dd-mmm" and you type "18/1" in that cell Excel will display "18-Jan". Note that the number is still 43118 which includes the year, but the year is suppressed by the cell's format. Without changing the value in the cell, change the format to "dddd" and the display will change to "Thursday".
Install the code below in the codesheet of the worksheet on which you want the action. Specify the column in which youn want changes to take place in the indicated line at the top of the code.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' 29 Jan 2018
' specify your date column here
Const TargetColumn As String = "D"
Dim Entry As String
Dim Sp() As String
Dim i As Long
With Target
' edit 1 cell at a time
If (.Cells.Count = 1) And (.Column = Columns(TargetColumn).Column) Then
Entry = .Value
Sp = Split(Entry, " to ")
For i = 0 To UBound(Sp)
If IsDate(Sp(i)) Then
Sp(i) = Format(Sp(i), "dd-mmm")
End If
Next i
.Value = Join(Sp, " to ")
End If
End With
End Sub
The code parses an entry into portions separated by " to ". There might be more than two. In each part, even if there is no "to", it looks for something Excel can understand as a date and formats it the way you want. You can modify the format in the code.
The resulting string is written to the cell just modified. It is no longer a true date which Excel could use to do calculations with..
Assuming that you have 15/01 in one cell and 18/01 in another cell, you could use a custom format (aka format cells). In the date formats, I had a YY/MM format, if you do not, create your own in the CUSTOM category YY/MM.