Anniversary date in a series of start and end dates

[Spaceballs_the_fanboy]

Hi folks, I am not sure how to Excel this problem.

I have a series of start and end dates throughout the year, and over other years. For example:
Start End Start End
2018-01-01 2018-04-01 2018-08-01 2018-12-01

I am trying to find the 6 month anniversay date. I am also using Network days only (holidays are not roo important).

Can Excel do this for me?

Thanks!

 

[Alice Morland]

Hi Fanboy,

I presume you have your data in columns - for example, Start Date is Column A and End Date is Column B.

To find the 6 month anniversary date for each line, you could create a new column (say, Column C), and (presume you're in row 2) fill it with this formula: =A2+ 182. Drag it until the last row.

Now Column C includes the 6 month anniversary dates.

Hope this helps.

Alice

 

[Spaceballs_the_fanboy]

Hi Alice, that is correct, however not exactly how the spreadsheet works. It looks more like this:
Column A = customer name
Column B = start date (Jan 20, 2018)
Column C = end date (Feb 12, 2018)
Column E = start date (April 3, 2018)
Column F = end date (June 15, 2018)
Column H = start date (July 9, 2018)
Column I = end date (November 16, 2018)

I am trying to find the anniversary date that falls somewhere in the series. I also would like to do the same for the 9th, 12th, 24th anniversaries as well.

Thanks for posting

 

[Alice Morland]

COuld you give me an example? Am I correct in assuming you'd want to calculate the date that falls 6 months after each start date?

 

[Spaceballs_the_fanboy]

If we use the example above. I am looking for the 6 month anniversary (network days) within the total series of start and end dates. For example:
Column A = customer name
Column B = start date (Jan 20, 2018)
Column C = end date (Feb 12, 2018)

Column D = 1 month*
*(for demonstration only the math is not correct)

Column E = start date (April 3, 2018)
Column F = end date (June 15, 2018)

Column G = 2.5 months*

Column H = start date (July 9, 2018)
Column I = end date (November 16, 2018)

Column J = 5 months*

So, the 6 month anniversary date lies somewhere between the start and end dates of Columns H, I. For this example lets say its October 20th. I am looking for a formula that can return the date of October 20th for me.

Thanks