How do I build a formula to count days from start to end from multiple columns w/o duplicating count

D Gray

I have a sheet that I am using to track delays for projects. There are twelve different delays that could be recorded. Each delay has a start date recorded and an end date recorded. There may be multiple delays going on at the same time.

In order to track how many days were effected by delays, I don't want to count each delay individually because then I am duplicating days as though they were in succession when they may have overlap.

How do I build a formula that would look at the earliest start date from all of the delays and the most recent end date to calculate a total number of days in delay?

Summer

Hi @D Gray

Without seeing how your sheets are outlined, I can't provide a tailored formula. From reading your question, this proposed approach may work for you.

First Day of Delay: =MIN([Delay Start]:[Delay Start]), Looks at the "Delay Start" column and returns the earliest date.

Last Day of Delay: =MAX([Delay End]:[Delay End]), Looks at the "Delay End" column and returns the latest date.

Duration: =NETDAYS([First Day of Delay]@row, [Last Day of Delay]@row), Returns the number of calendar days between the first day of delay and the last day of delay. If you want to calculate work days, consider replacing "NETDAYS" with "NETWORKDAY" or "NETWORKDAYS" depending on your needs.

D Gray

This is helpful but my delay types are recorded in columns (see below)...I need a formula that looks at each delay start column and each delay end column to determine the first and the last day of delay?

Summer

@D Gray

First Range: [Signage Delay Start Date]:[Signage Delay Start Date]

Second Range: [Space Plan Delay Start Date]:[Space Plan Delay Start Date]

The above references the entire column. If you want to limit the range simply add in the appropriate row numbers: [Signage Delay Start Date]5:[Signage Delay Start Date]10

First Day of Delay: =MIN([Signage Delay Start Date]:[Signage Delay Start Date], [Space Plan Delay Start Date]:[Space Plan Delay Start Date])

Last Day of Delay: =MAX([Signage Delay End Date]:[Signage Delay End Date], [Space Plan Delay End Date]:[Space Plan Delay End Date])

If you want the entire calculation in one formula as opposed to breaking it out into its separate components, it would look like this: =NETWORKDAY(MIN([Signage Delay Start Date]:[Signage Delay Start Date], [Space Plan Delay Start Date]:[Space Plan Delay Start Date]), MAX([Signage Delay End Date]:[Signage Delay End Date], [Space Plan Delay End Date]:[Space Plan Delay End Date]))

D Gray

Actually I figured it out...I added a first day of delay and a last day of delay column, then I used the MIN and MAX functions to look at the cells in each column of that row to return the value (see below). Then I have a total days cell that subtracts the MIN Date from the MAX Date. Thank you. This was helpful.

=MIN([On Hold Start Date]@row, [Signage Delay Start Date]@row, [Space Plan Delay Start Date]@row, [Waivers Delay Start Date]@row, [Landlord Delay Start Date]@row, [Internal Approval Delay Start Date]@row, [Other (Co-Tenant) Delay Start Date]@row, [Other (Existing Tenant) Delay Start Date]@row, [Construction Cost/Site Eval Delay Start Date]@row)

=MAX([On Hold End Date]@row, [Signage Delay End Date]@row, [Space Plan Delay End Date]@row, [Waivers Delay End Date]@row, [Landlord Delay End Date]@row, [Internal Approval Delay End Date]@row, [Other (Co-Tenant) Delay End Date]@row, [Other (Existing Tenant) Delay End Date]@row, [Construction Cost/Site Eval Delay End Date]@row)

=[Last Day of Delay]@row - [First Day of Delay]@row