Formula based on timelines (start and end dates)

Sborror

I'm trying to basically show sr. mgnt how many high project projects are all happening at the same time -- ideally I'd like to count them -- is there an easy way to do that?

Essentially I'm using project type = High then want to show those start and end dates that fall w/in maybe a quarter range. Is that possible?

TIA!

MCorbin

I've done something similar - my boss wanted counts of projects that were "Open" last week, 2 weeks ago, 3 weeks ago, etc...

Here's the one that looks to see if it was open last week

=IF(ISDATE($[Actual Start Date]@row), IF($[Actual Start Date]@row <= TODAY(7 - WEEKDAY(TODAY()) - 7), IF(ISDATE($[Actual Complete Date]@row), IF($[Actual Complete Date]@row < TODAY(1 - WEEKDAY(TODAY()) - 7), "No", "Yes"), "Yes"), "No"), "No")

In your case, if you're comparing to start & finish dates for a quarter, here's what I would do:

Add 2 helper columns for Quarter Start (QS) and Quarter End (QE) Dates.

Then you could substitute in my formula:

=IF(ISDATE($[Actual Start Date]@row), IF($[Actual Start Date]@row <= $QE$1, IF(ISDATE($[Actual Complete Date]@row), IF($[Actual Complete Date]@row < $QS$1), "No", "Yes"), "Yes"), "No"), "No")

Basically, this is looking to make sure that both my Actual Start and Actual Finish dates have dates populated in them.

If Actual Start has a date, it compares it to the Quarter End Date - if it started before the end of this quarter, it moves on...

Then it compares the Actual Complete Date. If it's blank (assuming it's still being worked on), and the Actual Start date was before the end of the quarter, you get a "Yes"

If the Actual Complete Date has a date in it, and the date was before the Start Date of the Quarter (in this case, it started and finished before the quarter started), then you get a No.

Otherwise, if Actual Start date is before the Quarter End date and Actual Complete date is after the quarter Start date, then you get a Yes.

(And yes, this was probably the long way around, but it works) :-D

Sborror

So I think I get what you suggest but don't know how to set up the Qtr end date and Qtr start date

Genevieve P.

Hi @Sborror

It looks like @MCorbin set up 2 helper Date Columns to create a sort of chart: one titled QS to indicate the Quarter Start date, the other titled QE to indicate the Quarter End date, then with the selected dates in row 1. This is why there are absolute references (the $ signs) in the formula: IF($[Actual Start Date]@row <= $QE$1

Cheers,

Genevieve

MCorbin

Yes, that's exactly what I would do.

I have a "helper" sheet where I have all sorts of date related values. I use these values in lookups and other formulas to help drive monthly and quarterly totals (among other things)

You list start/finish dates for each quarter like I've done in the last 3 columns here, and then do your counts by quarter comparing your project start and end dates to these dates. (By the way, I'd do that in either a Sheet Summary or a separate metrics sheet)

Paul Newcome

If you have your quarter dates established such as in a table similar to @MCorbin's last screenshot, you could just reference that directly in your COUNTIFS without having to try to establish quarters on the source sheet.

Q1:

=COUNTIFS({Source Sheet END DATE}, @cell >= [Quarter Start Dt]1, {Source Sheet START DATE}, @cell <= [Quarter End Dt]1)

Basically you take the data's start date and look for less than the period end date and the data's end date being greater than the period's start date.