Return a value vased on dates

[sdzolin]

I am very new.  

I work on a college campus and I have a form that has a date column.  I want to create a column that converts the date to the semester in which that date occurs.  So for example if the form was completed for January 4, 2024 that would be "Winter 2024"  I am trying to figure out how to specify which dates go to which semester and then have it return the correct value for the date.

[sdzolin]

How would this work if the crossovers are mid-month.  For example for the 2023-2024 academic year the dates are as follows

Fall 2023 = Aug 27 - Dec 19

Winter 2024 = Dec 20 - Jan 20

Spring 2024 = Jan 21- May 17

Summer 2024 = May 18 - Aug 24

[ronrsnfld]

You can create a table, and then use that to lookup the relevant date.

In the code below I hard-coded the table, but there are many ways to create it.

The table has three columns Semester | dtStart | dtEnd.

For example, as a function (named: fnSemester)

(dt as date)=>

let 
    semesterTable= Table.FromRecords(
        {[Semester="Fall 2023", dtStart=#date(2023,8,27), dtEnd = #date(2023,12,19)],
         [Semester="Winter 2024", dtStart=#date(2023,12,20), dtEnd=#date(2024,1,20)],
         [Semester="Spring 2024", dtStart=#date(2024,1,21), dtEnd=#date(2024,5,17)],
         [Semester="Summer 2024", dtStart=#date(2024,5,18), dtEnd=#date(2024,8,24)]},
         type table[Semester=text, dtStart=date, dtEnd=date]),

    pickSemester = Table.AddColumn(semesterTable,"Date Semester", each dt >= [dtStart] and dt <= [dtEnd], type logical)

in 
    try Table.SelectRows(pickSemester, each [Date Semester]=true)[Semester]{0} otherwise "Not in Current Table"

and then, in the main code body

let
    Source = Table.FromColumns(
        {{#date(2023,11,30), #date(2024,1,15),#date(2024,9,20)}},
        type table[Dates=date]
    ),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Semester", each fnSemester([Dates]), type text)
in
    #"Invoked Custom Function"

Result

[dufoq3 · Accepted solution]

Of course 😉

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTVNzIwMlaK1QFzjQyRuPomII4JmGOmb2gG5cUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Semesters = 
        Table.Buffer(Table.SelectColumns(Table.ExpandListColumn(Table.AddColumn(#table(type table[Month From=Int64.Type, Day From=Int64.Type, Month To=Int64.Type, Day To=Int64.Type, Semester=text], {
                {8,27, 12,19, "Fall"},
                {12,20, 1,20, "Winter"},
                {1,21, 5,17, "Spring"},
                {5,18, 8,26, "Summer"}
                }),
            "DateCode", each 
                [ from = #date(if [Semester]="Winter" then 2019 else 2020, [Month From], [Day From]),
                  to = #date(2020, [Month To], [Day To]),
                  dates = List.Dates(from, Duration.Days(to-from)+1, #duration(1,0,0,0)),
                  dateToNumber = List.Transform(dates, (x)=> Number.From(Date.ToText(x, "MMdd")) )
                ][dateToNumber], type list   
        ), "DateCode"), {"DateCode", "Semester"})),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    Ad_Semester = Table.AddColumn(ChangedType, "Semester", each 
        [ a = Number.From(Date.ToText([Date], "MMdd")),
          b = Semesters{[DateCode = a]}[Semester],
          c = b & " " & Text.From(if b = "Winter" and a >= 1219 then Date.Year(Date.AddYears([Date], 1)) else Date.Year([Date]) )
        ][c], type text)
in
    Ad_Semester

[dufoq3]

Hi @sdzolin, try this:

Result

If you have different semesters, edit them here:

Left number is semester starting month, right number is semester ending month. Do not overlap months!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTVNzIwMlaK1QFy9U1AHBMwx0zf0AzKiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Semesters = Table.RemoveColumns(Table.ExpandListColumn(Table.AddColumn(#table(type table[Month From=Int64.Type, Month To=Int64.Type, Semester=text], {
        {9, 1, "Winter"},
        {2, 6, "Summer"}
   }), "Months", each if [Month From] > [Month To] then {[Month From]..12} & {1..[Month To]} else {[Month From]..[Month To]}, type list), "Months"), {"Month From", "Month To"}),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}}, "en-US"),
    Ad_Semester = Table.AddColumn(ChangedType, "Semester", each Semesters{[Months = Date.Month([Date])]}[Semester] & " " & Text.From(Date.Year([Date])), type text)
in
    Ad_Semester