1 Reply Latest reply on Sep 26, 2016 3:36 PM by kettan

    # of days in year upto end of previous month

    Vishal D

      # of days in year upto end of previous month

       

      My current formula

       

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

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

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

       

      How do adjust the formula so that it calculates # of days upto end of previous month end. e.g if they run today 9/26/2016. It should calculate # of days upto Aug 31 2016

       

      This should happen only for current year

        • 1. Re: # of days in year upto end of previous month
          kettan

          This returns August 31, 2016:

           

          DATETRUNC('month',TODAY())-1
          

           

          This returns 244 for day of year of August 31, 2016:

           

          DATEPART('dayofyear',DATETRUNC('month',TODAY())-1)
          

           

          This I think returns what you want:

           

          IF [Report Year] = YEAR(TODAY()) THEN
             DATEPART('dayofyear',DATETRUNC('month',TODAY())-1)
          ELSE
             DATEPART('dayofyear',DATEADD('year',[Report Year]-2000,#2000-12-31#))
          END