Different Approach to Date Calculation

don't use an awkward [Year Day No]  - use an awkward "Days since 12/30/1899" number which you get when you convert your date column to "Whole Number" . That also works across year boundaries.

Hi, 

Thanks for the solution lbendlin  provided, and i want to offer some more information for user to refer to.

hello Grimfandango227 , based on your desscrition, you don't need to conver the date the whole number, you can change your code to the following.

 =Table.AddColumn(#"Changed Type1", "Daily State", each let _today=DateTime.Date(DateTime.LocalNow()),
 _7today=Date.AddDays(_today,-7),
 _13today=Date.AddDays(_today,+13),
 _format=Number.ToText(Date.Month([Ending_date]))&Number.ToText(Date.Day([Ending_date]))
in   if [Ending_date]<_7today then "Late" else if [Ending_date]>_13today then "Future" else if [Ending_date]=_today then "Today" else _format)

Output

And you can refer to the attachments.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.