6 Replies Latest reply on Jan 12, 2017 2:05 AM by Dhruv Gupta

    # of days in current month calculation

    Vishal D

      # of days in Month is calculating 1 day less for current month.

       

      Today's date = 1/11/2017 and I am expecting 11 in # of days in Month

       

      Not sure what I am missing in formula?

       

      # of year calc is calculating is doing it right.

       

       

      # of days in Month = datediff('day',date(str([Report Year])

      + '-' + str([Report Month]) + '-01'),

      (IF(INT([Report Month])= DATEPART('month',NOW())

      AND(INT([Report Year])= DATEPART('year',NOW())))

      THEN DATE(str([Report Year])

      + '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))

      ELSE DATEADD('month',1,date(str([Report Year])

      + '-' + str([Report Month]) + '-01'))END))

       

      # of days in Year =

      DATEDIFF('day',date(STR([Report Year])+'-01-01'),

      IF ([Report Year]=DATEPART('year',NOW()))

      THEN NOW()ELSE DATE(STR([Report Year])+'-12-31')end)+1

        • 1. Re: # of days in current month calculation
          Tom W

          Your Year calculation for January 2017 is comparing 2017-01-01 and Now(). That evaluates out to be 10 days.

          To prove it, create this calculation - datediff('day',#2017-01-01#,now())

           

          The difference between 2017-01-01 and 2017-01-11 is 10 days, not including the ending day.

           

          Your year calculation has a +1 on the end of it but your month calculation does not.

          • 2. Re: # of days in current month calculation
            Vishal D

            If i add +1 for month woudnt it add +1 to all prior months which are calculating correct.

             

            Only the current month is not coming right.

             

            how should me below formula be adjusted?

             

            # of days in Month = datediff('day',date(str([Report Year])

            + '-' + str([Report Month]) + '-01'),

            (IF(INT([Report Month])= DATEPART('month',NOW())

            AND(INT([Report Year])= DATEPART('year',NOW())))

            THEN DATE(str([Report Year])

            + '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))

            ELSE DATEADD('month',1,date(str([Report Year])

            + '-' + str([Report Month]) + '-01'))END))

            • 3. Re: # of days in current month calculation
              Andrew Watson

              It gets quite ugly with the manipulation to the end date or the +1. You could just remove a day from the start date instead, maybe a little cleaner. It's still quite inelegant for your month when not this month as it needs taking back to the last day of the month rather than the first day of the next month, which you were doing.

               

              Check the syntax as these aren't tested.

               

              For example

               

              # of days in Year =

              DATEDIFF('day',DATEADD('day',-1,date(STR([Report Year])+'-01-01')),

              IF ([Report Year]=DATEPART('year',NOW()))

              THEN NOW()ELSE DATE(STR([Report Year])+'-12-31')end)

               

              # of days in Month = datediff('day',DATEADD('day',-1,date(str([Report Year])

              + '-' + str([Report Month]) + '-01')),

              (IF(INT([Report Month])= DATEPART('month',NOW())

              AND(INT([Report Year])= DATEPART('year',NOW())))

              THEN DATE(str([Report Year])

              + '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))

              ELSE DATEADD('day',-1,DATEADD('month',1,date(str([Report Year])

              + '-' + str([Report Month]) + '-01')))END))

              • 4. Re: # of days in current month calculation
                Jonathan Drummey

                One possible efficiency gain on Andrew's answer: If your date source supports it the MAKEDATE(<year>, <month>, <day>) function is really helpful in avoiding all those string conversions that can make calcs 1,000x slower than using date functions or processing dates as math. For example:

                 

                DATE(str([Report Year]) + '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))

                 

                Would become:

                 

                MAKEDATE([Report Year], [Report Month], DAY(TODAY()))

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: # of days in current month calculation
                  Vishal D

                  Hi John,

                   

                  How would I update below formula to use MAKEDATE function?

                   

                  # of days in Month = datediff('day',DATEADD('day',-1,date(str([Report Year])

                  + '-' + str([Report Month]) + '-01')),

                  (IF(INT([Report Month])= DATEPART('month',NOW())

                  AND(INT([Report Year])= DATEPART('year',NOW())))

                  THEN DATE(str([Report Year])

                  + '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))

                  ELSE DATEADD('day',-1,DATEADD('month',1,date(str([Report Year])

                  + '-' + str([Report Month]) + '-01')))END))

                  • 6. Re: # of days in current month calculation
                    Dhruv Gupta

                    Hi Vishal,

                     

                         I think What john meant is to convert [Report Year], [Report Month] in date format(using some function similar to MakeDate) at the database level

                    e.g. [Report Date]= MAKEDATE([Report Year], [Report Month], DAY(TODAY()))

                    and use that date variable in the said formula like Year([Report Date]), Month([Report Date]).

                     

                     

                    Thanks,

                    Dhruv