Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Function days360 with current date

I would like to create a column which counts the days from today till a certain date. And devide the margin by the number of days to create a certain ranking.     I will upload a table which cont...
Need Help
  • Interkoubess's avatar
    Interkoubess
    8 years ago

    Hi Anonymous,

     

    Please create a calculated column with the formula below I created a column with today value Tdate:

     

    =DATEVALUE([Estimated first delivery (EFD)])-DATEVALUE([Tdate])

    Let us know if it does not work.

     

    For your second request yes an if can resolve it.

     

    Ninter

  • Anonymous's avatar
    Anonymous
    8 years ago

    Thank you Interkoubess

    It is working the way I wanted. Just had to switch the column from date -> whole number in modeling.

     

Interkoubess's avatar
Interkoubess
Solution Sage
8 years ago

Hi Anonymous,

 

Please use datediff to compute the days till EFD ( create a column) and then for ranking use Marging/Days till EFD.

 

let us know if it does not work.

 

Ninter.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Interkoubess

     

    Thank you very much for your reply.

    When I try to do this it shows the error: In DATEDIFF function, the start date cannot be greater than the end date.

     

    I think this derives from the fact that some dates in the EFD column are in the past. This is because this colomn is manually filled by employees and sometimes they forget to update this.

     

    The dates in the past should go either to -x or 0

    Is there a way to still make this work with past dates as well?

     

    • Interkoubess's avatar
      Interkoubess
      Solution Sage
      8 years ago

      Hi Anonymous,

       

      Could you please give sample with the data that are not working, I will make a try and let you know.

       

      Ninter.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Interkoubess

         

        The same as the data above. But than with EFD in the past

         

        Company        Margin         Estimated first delivery (EFD)     |     days till EFD         Ranking

        Company A      10000            11-Feb-2018                                  |     31                        322,5806

        Company B      15000            13-jan-2018                                   |     3                          5000

        Company C      12000            12-Dec-2017                                  |     -29                       -413,793

        Company D        9000            02-Jan-2018                                  |     -8                         -1125

         

        In stead of heaving a - ranking I might even prefer to have a 0 valeu. But I assume this can be easaly done by IF(