Is there a way to sort seasons into a list of dates?

Curious Cat

New Member
Joined
Mar 25, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
I have a list of magazines by release date in a column as Jan-20, Feb-20, etc. These are straightforward to sort, however I want to inculde seasonal editions in this list too that will be tied to a particular date without having to add in a second column. Is there any workaround that get it to recognise winter, summer, spring and autumn as a set date and display that text instead of the date where relavent? E.g. so if I could put in 1 March 2021 I'd get the date as Mar-21, but if I entered 2 March 2021 instead of getting the date it would convert it to Spring?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
367
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you right click the sheetname tab and click view code and paste the code below will create an event driven subroutine. It assumes the column you want these conversions done is E. Change the 5 in the first line of code to the appropriate column number for your spreadsheet. Any dates entered into that column with a day value of 2 will be converted as defined in the select case section. Non-dates or deletions in that column will cause an error and the code will exit without doing anything

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim x As String
    If Target.Column <> 5 Or Target.Count > 1 Then Exit Sub
    On Error GoTo ExitSub
    If Day(Target.Value) = 2 Then
        Select Case Month(Target.Value)
            Case 12, 1, 2
                x = "Winter"
            Case 3, 4, 5
                x = "Spring"
            Case 6, 7, 8
                x = "Summer"
            Case 9, 10, 11
                x = "Fall"
            Case Else
                Exit Sub
        End Select
        Application.EnableEvents = False
        Target.Value = x
        Application.EnableEvents = True
    End If
ExitSub:
                
End Sub
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,108
Messages
5,768,142
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top