Date range custom 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.



Selected Answer

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..



Thank you but I am looking for a specific type of format like "dd/mmm to dd/mmm" (Of course this is not working).
Coscongr (rep: 2) Jan 29, '18 at 12:06 am
Aaah! You want two dates in one cell. That is anti-Excel because one would need to unravel the concoction for further processing of the numbers. In fact, there won't be any numbers to process, just strings.
But if that is what it takes to make you happy, come back in half an hour. I will add code to my answer that makes it possible.
Variatus (rep: 3758) Jan 29, '18 at 1:04 am
Add to Discussion

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.


Answer the Question

You must create an account to use the forum. Create an Account or Login