Forum Discussion

Grimfandango227's avatar
Grimfandango227
Frequent Visitor
2 years ago
Solved

Different Approach to Date Calculation

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

  

 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.