Forum Discussion

weesgaard's avatar
weesgaard
Frequent Visitor
9 years ago
Solved

Calculate days per month based on date interval

I need help finding the right formula to calculate number of days per month based upon my "IndDato" and "UdDato".

If the "IndDato" shows 15-01-2017 and "UdDato" shows 30-06-2017, I need a column for January showing 16 days, a column for February showing 28 days and so on. If the "UdDato" is blank I need the full number of days for the month to be shown.

 

My table looks like this:

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi weesgaard,

    As I test, your TG 01,17 returns the error message as follows.



    Please create 01,17 using the formula, you will get expected result.

    01.17 = IF(Lejer[IndDato]<=DATE(2016,12,31),IF(Lejer[IndDato]<DATE(2017,02,1),DATEDIFF(DATE(2017,1,1),Lejer[UdDato],DAY),31),IF(Lejer[UdDato]<DATE(2017,02,1),DATEDIFF(Lejer[IndDato],Lejer[UdDato],DAY),DATEDIFF(Lejer[IndDato],DATE(2017,1,31),DAY)))



    Then you can get the 02,17 using the similar formula.

    Best Regards,
    Angelia

9 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi weesgaard,

    Based on my understanding, if "IndDato" shows 15-01-2017 and "UdDato" shows 30-06-2017, you will add 16 days for Jan, 28 days for Feb, 31 days for Mar, 30 days for Apr, 31 days for May, and 31 days for Jue, right? If it is, you need to create mutiple calculated columns? And the columns are different for different "IndDato" and "UdDato". It's hard to achieve this, there is no regular rules here.

    Best Regards,
    Angelia

    • weesgaard's avatar
      weesgaard
      Frequent Visitor
      9 years ago

      Hi Angelia

      Thanks for your reply.

      You are absolutely correct about multiple calculated columns.

       

      I might have found a formula that works for january:

      TG 01.17 = IF(AND(Lejer[UdDato]>DATE(2016;12;31);Lejer[IndDato]<DATE(2017;02;1));CALCULATE(SUM(Lejer[UdDato])-CALCULATE(SUM(Lejer[IndDato])))+IF(Lejer[IndDato]<DATE(2017;01;01);CALCULATE(SUM(Lejer[IndDato])-DATE(2017;01;01)))-IF(Lejer[UdDato]>DATE(2017;01;31);CALCULATE(SUM(Lejer[UdDato])-DATE(2017;02;1))))

       

      However when I try to dublicate it for a new column "TG 02.17" and I change the date intervals in the formula - I get en error on circular references. Do you know why?

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi weesgaard,

        Could you please share your formula to create column "TG 02.17" for further analysis?

        Best Regards,
        Angelia