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

# End Date Formula

Hi,

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

Example of current data:

ValueAll_date_date4_CTLProject
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

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.

Courtney

• ###### 1. Re: End Date Formula

Hi Courtney

See if this will work for you.

Thanks

Mike

• ###### 2. Re: End Date Formula

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!

Courtney

• ###### 3. Re: End Date Formula

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

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)

-Courtney

• ###### 5. Re: End Date Formula

More like this?

• ###### 6. Re: End Date Formula

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!

-Courtney

• ###### 7. Re: End Date Formula

I think this might work.