IF AND formula to compare multiple start and end dates.

SteveE

Our year is broken into accounting periods which differ from calendar year/month. For example, period 1 starts on 12/30/23 and ends on 1/26/24.

In my project plan, I look at the start and end dates of project plan tasks and determine what period it falls in. I reference a separate sheet with the period start/end dates (e.g., P1 Start, P1 End, P2 Start, P2 End …. P12 Start, P12 End).

The formula below works if a task falls in a single period. If the task overlap periods, I insert “multiple periods”. 

=IF(AND([Start Date]@row >= {P1 Start}, [End Date]@row <= {P1 End}), "1", IF(AND([Start Date]@row >= {P2 Start}, [End Date]@row <= {P2 End}), "2", IF(AND([Start Date]@row >= {P3 Start}, [End Date]@row <= {P3 End}), "3", IF(AND([Start Date]@row >= {P4 Start}, [End Date]@row <= {P4 End}), "4", IF(AND([Start Date]@row >= {P5 Start}, [End Date]@row <= {P5 End}), "5", IF(AND([Start Date]@row >= {P6 Start}, [End Date]@row <= {P6 End}), "6", IF(AND([Start Date]@row >= {P7 Start}, [End Date]@row <= {P7 End}), "7", IF(AND([Start Date]@row >= {P8 Start}, [End Date]@row <= {P8 End}), "8", IF(AND([Start Date]@row >= {P9 Start}, [End Date]@row <= {P9 End}), "9", IF(AND([Start Date]@row >= {P10 Start}, [End Date]@row <= {P10 End}), "10", IF(AND([Start Date]@row >= {P11 Start}, [End Date]@row <= {P11 End}), "11", IF(AND([Start Date]@row >= {P12 Start}, [End Date]@row <= {P12 End}), "12", IF(AND([Start Date]@row = "", [End Date]@row = ""), "Missing Dates", IF([Start Date]@row = "", "No Start Date", IF([End Date]@row = "", "No End Date", "Multiple Periods")))))))))))))))

Here is sample of current output:

I’d like to go to further and for overlapping periods, put each of the periods (e.g. “1,2”). The start date will give me the first period, but I’m not certain how to determine/compare the end date. Do I need to do the end date compare for all 12 periods? And what would that look like.

Thought I’d ask for help in case there is a better/easier formula to use.

Thanks in advance for any input, thoughts, etc.

Scott Orsey

Hi,

That's a pretty large formula to begin with so I would consider a different approach. Have you considered making two columns, one that returns the period of the Start Date and one that returns the period of the End Date?

To make your life easier, you could look into using the range for the cross sheet reference instead of individual cell links. You could then use MATCH (if all you want is a the period number) or INDEX(MATCH) if you wanted whatever appears in the Period column.(eg if you wanted it to say "Per 1" instead of just "1"). Here is what MATCH would look like:

StartPeriod= MATCH([Start Date]@row,{Start Date Range from Other Sheet},1)

[Search Type] = 1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)

Using INDEX(MATCH):

StartPeriod= INDEX({Period Range from Other Sheet},MATCH([Start Date]@row,{Start Date Range from Other Sheet},1) )

You would do the same for End Date.

Then you could create a column to merge these two together in something like:

ProjectPeriod= IF(StartPeriod = EndPeriod, StartPeriod, StartPeriod +" - " + EndPeriod)

This would return a single value (eg "1") if the start and end were in the same period and a range (eg "1-3") if they were in different periods.

Good Luck,

Be well

SteveE

@Scott Orsey - Thank you very much for the idea!!! That is much easier path to go down!

Scott Orsey

Hi,

That's a pretty large formula to begin with so I would consider a different approach. Have you considered making two columns, one that returns the period of the Start Date and one that returns the period of the End Date?

To make your life easier, you could look into using the range for the cross sheet reference instead of individual cell links. You could then use MATCH (if all you want is a the period number) or INDEX(MATCH) if you wanted whatever appears in the Period column.(eg if you wanted it to say "Per 1" instead of just "1"). Here is what MATCH would look like:

StartPeriod= MATCH([Start Date]@row,{Start Date Range from Other Sheet},1)

[Search Type] = 1: (The default value) Finds the largest value less than or equal to search_value (requires that the range be sorted in ascending order)

Using INDEX(MATCH):

StartPeriod= INDEX({Period Range from Other Sheet},MATCH([Start Date]@row,{Start Date Range from Other Sheet},1) )

You would do the same for End Date.

Then you could create a column to merge these two together in something like:

ProjectPeriod= IF(StartPeriod = EndPeriod, StartPeriod, StartPeriod +" - " + EndPeriod)

This would return a single value (eg "1") if the start and end were in the same period and a range (eg "1-3") if they were in different periods.

Good Luck,

Be well

SteveE

@Scott Orsey - Thank you very much for the idea!!! That is much easier path to go down!