Different Approach to Date Calculation

[Grimfandango227]

Hello, 

I currently have a report that essentially takes two values [Year Day No] and [Today Year Number]

[Year Day No] is the year and day number merged of the ending date of a production order

[Today Year Number] is the year and day number of today's date merged. 

My current calculation can be seen below which is wrong. 

                          

Essentially I was using this to get my "Daily State" outcome, the problem is of course when the end date of a production order is in 2025 and in the beginning of the year the [year day number] will be smaller than the [today year number] thus resulting in a "Late" when it really is not. Example below. 

           

I know this is probably very confusing, but I need to find a way to re-calculate this so that my "Daily State" is correct. 

Any help would be much appreciated!

Thanks

Jeremy

[lbendlin · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[lbendlin · Accepted solution]

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.