7 Replies Latest reply on Apr 15, 2019 1:18 PM by mike fuqua

    End Date Formula

    Courtney Zessar



      I have a data set that basically has columns with a person's hours worked (Value), the person's name (4_CTL), the date (All_date_date), and the Project they are working on (Project).


      • Value: A number between 0 and 11
      • All_date_date: Every date within the time frame I'm looking at
      • 4_CTL: Just names repeated for each date with the person's hours (Value)
      • Project: Due to the initial nature of my data set, values of 0 still have a Project name next to them because they fill out a line of all dates
        • Data used to look like the following:
        • 4_CTLProject4/1/20194/2/20194/3/20194/4/20194/5/2019
          CourtneyProject Dragon0011110
          CourtneyProject Chair00005.5
          AlexProject Desk1111000
          CharlesProject Cookie011000


      Example of current data:


      04/1/2019CourtneyProject Dragon
      04/2/2019CourtneyProject Dragon
      114/3/2019CourtneyProject Dragon
      114/4/2019CourtneyProject Dragon
      04/5/2019CourtneyProject Dragon
      04/1/2019CourtneyProject Chair
      04/2/2019CourtneyProject Chair
      04/3/2019CourtneyProject Chair
      04/4/2019CourtneyProject Chair
      5.54/5/2019CourtneyProject Chair
      114/1/2019AlexProject Desk
      114/2/2019AlexProject Desk
      04/3/2019AlexProject Desk
      04/4/2019AlexProject Desk
      04/5/2019AlexProject Desk
      04/1/2019CharlesProject Cookie
      114/2/2019CharlesProject Cookie
      04/3/2019CharlesProject Cookie
      04/4/2019CharlesProject Cookie
      04/5/2019CharlesProject Cookie



      I am hoping to write a calculation that creates a column that tells the end date of a project. Essentially, it would sum all the hour values and take the date when the sum of values is maxed (please note that there could be more than one person on a project). Basically it will just return the last date then. Something along the lines of (but unsure how to write it):


      {fixed [4_CTL] : MAX(sum([Value])) THEN max([All_Date_Date]) }


      Please let me know the best way to write this calculation.


      Thanks in advance!


        • 1. Re: End Date Formula
          mike fuqua

          Hi Courtney


          See if this will work for you. 




          • 2. Re: End Date Formula
            Courtney Zessar

            Hi Mike,


            Thanks for your reply. I'm trying to get the calculation to show the end date of a project. I believe this is showing the max number of hours instead? Does this calc need to be flipped in some way?


            Thanks again!


            • 3. Re: End Date Formula
              mike fuqua

              What it's doing is creating a boolean for a max date of the project.  If it's true then it's summing all the hours.  I guess I wasn't understanding your original ask.  So, you want to find when the hours of a project is maxed out?  What criteria are you using to determine when the hours have reached the max?   If you just need to see the end date of a project you could use { FIXED [Project]: MAX([All date date])}.  Hopefully this is closer to what you're needing.

              • 4. Re: End Date Formula
                Courtney Zessar

                Since this is a spreadsheet for a set date range, there are dates for every single project, even if it is not occurring on those days (i.e. the dates of the spreadsheet I'm working with goes from 3/19/2019 to 7/4/2019, but projects only occur in spurts within that time frame). As illustrated in the above original data, there would just be 0's before or after a person's hours allocated to that project, if the project is not occurring on those days.


                I get your idea, but could we make it so it max's the hours (Value) for the project, and selects the date in which the hours are at maximum (because that would be the last day of the project)?


                Something like....


                if { FIXED [Project]: MAX([sum(value)])} then max(all_date_date)


                Really appreciate your help, thanks!



                • 5. Re: End Date Formula
                  mike fuqua

                  More like this? 

                  • 6. Re: End Date Formula
                    Courtney Zessar

                    So when I use that, I just end up with the last date in the entire range (for my whole data set 7/6) for every single project. I want it to instead just spit out the last date that there is a number recorded for that project that is greater than 0 (I guess that's another way of explaining it).


                    Let me know if that makes sense, thanks!



                    • 7. Re: End Date Formula
                      mike fuqua

                      I think this might work.