How to rank dates

William.T_Genisium

Hi, I could not find any posts regarding this.

Basically, I am making a sheet for managing projects where, for each task, there is multiple gates (step 1, step2, etc.) which have multiple dates (actual advancement date, baseline [first estimate], forecast [estimate based on actual advancement] and planned [ updated estimate if the forecast is too far behind the baseline]).

I want to have a cell that returns the baseline gate (which gate we should be at) based on a date (Called Schedule_Planning_Date).

The solution I tought of was ranking the value of the schedule planning date (S_P_D)in the range of baseline gates.

so if S_P_D = 2023-10-4, baseline step 1 is 2023-09-12 and baseline step 2 is 2023-11-20, Baseline Gate would be ranked 2nd in that range, wich combined with an index formula, would return Step 2, wich means that on the S_P_D, according to the baseline, we should be working on step 2.

I will add screenshots in the comments.

Thank you in advance:)

Paul Newcome

Screenshots would definitely help.

William.T_Genisium

Here is the original formula in Excel

William.T_Genisium

Here are the screenshots of the smartsheet for reference

Paul Newcome

I'm still not sure I follow what you are trying to do. Are you wanting to output the next date, or some text to indicate what the next gate is based on dates being entered?

William.T_Genisium

I want to output the gate that would be after the Schedule_Planning_Date.

Lets say the S_P_D is 15/10/23, what I would want to output is "Issued For Tender Baseline"

I already have a reference for the gate names that I can use in an Index({Ref}, XXX)

So basically, I would need to output the rank of the S_P_D date among the Gates listed.

Paul Newcome

Can you provide a screenshot of the sheet that has the gate names you can reference in the INDEX?

William.T_Genisium

Here you go,

Paul Newcome

I don't see any dates in there.

Are you able to provide some screenshots with data manually entered that reflects what you are trying to accomplish?

William.T_Genisium

The dates are in the mother sheet if I can call it that. It is the picture with the green, you can see the fifth row are dates. The first four rows are parent rows and the next rows are another type of Deliverables, so the dates are further right in the sheet.

Hope this answers your question.

My biggest problem is that the Rankeq function only allows numbers to be ranked and not dates. One fix I can think of would be to use additionnal columns to join the year, month and date (2023-10-05 -> 20231005) That way it would be a number that I could rank. The problem is that I have so many date columns, this would certainly not be optimal.

p.s. I apologize if my English can be hard to understand, it is my second language.

Thanks again for your help, it is really appreciated:)

Paul Newcome

Ok. Got it. So based on that previously mentioned screenshot (5th row of snippet with green row), you want to output a status based on which date is filled in? Or will all dates be filled in and you are you looking to output a status based on which date most recently has passed?

William.T_Genisium

So, since this is the Baseline for the projet, these date would be filled manually at the start of the projet. Making it possible to view how the project should advance in time.

The Cell/formula I am stuck on is "Baseline Gate" wich should output the latest baseline Gate in relation to the Scehdule_Planning_Date, wich is entered manually when we want to make a monthly progress review for exemple.

Lets say I want to make a progress review for the mopnt of september. I would change the S_P_D date to 30th of september. Then, the Baseline gate should output "Issued for information Baseline" wich is the closest (<=) to the S_P_D that I entered.

William.T_Genisium

I currently have a lead towards a solution.

Here I tried a CountIF that would count the number of dates in the entire baseline Calendar that are less than or equal to the Schedule planning date.

In the exemple above, there would be 2 dates that are less than or equal to the 30th of septembre so the formula should output "2". However, I have an unparseeable problem.

William.T_Genisium

A countM works, as you can see with the 1 and the 5 in the column, so the problem is with the <= Schedule Planning Date [Index({tgConstants - Value}, Match("Schedule Planning Date", {tgConstants - Constant}))]

William.T_Genisium

I got it myself!!

The answer to my original question is : in order to perform a rank with dates, you have to use a COUNT formula with a COLLECT to collect the values that are less than or equal (basically a DIY RANKEQ).

For some reason, COUNTIF ans COUNTIFS would not work, I guess it has something to do with the fact that my values are dispersed in multiple ranges.

Unfortunately, the COLLECT function makes it so that you have to repeat the range twice, wich makes the formula redundant and long, but I have not found a way arround it yet.

William.T_Genisium

Also, everything you are trying to compare HAS to be in the same format (in my case, the column has to be formatted to dates)