Calculating Dates Between Months

**BizProcessGuy** · 06-28-2017, 09:32 AM

Hi, Thank you for clicking. I am working on a project where I need to calculate the amount of days impacted in prior or future months for altered contracts. The accounting team doesn't need to know about contract adjustments in the current month, but they need to know how much money to adjust for the previous or next month after a contract start/end date changes. To put it in perspective, I work for an equipment rental company and often we alter contracts due to equipment breakdowns or customer delays. They might have wanted to rent something that day and we have a contract they agreed to, but then the weather calls for rain and they delay a few days. We alter the contract as opposed to zeroing out the contract and re-typing. I need to write a formula which will capture the following in a cell so accounting has more visibility into the true revenue every month:

-If a contract is written on 6/1 and backdated to 5/30, the cell needs to say "2" since 2 days were added to the previous month.

-If a contract was written on 6/5 and backdated to 6/1, the cell should say "0" because the prior month was not impacted.

-If a contract was written on 6/1 and backdated to 4/1, the cell should say "61" since May and April were impacted.

-If a contract was written on 6/5 and backdated to 4/1, it would still need to say "61" because only the closed books from May and April were impacted.

-If a contract was written on 6/30 and forward-dated to 7/2, the cell should say "2" because 2 days were impacted in the next month.

-If a contract was written on 6/1 and forward-dated to 6/5, the cell should say "0" since it is still in the current month's open books.

-If a contract was written for 6/1 and forward-dated to 8/1, the cell should say "32" because all of July and August were impacted.

-If a contract was written on 6/5 and forward-dated to 8/1, the cell should still say "32" because that's the amount of days in the next months that were impacted.

Attached is the file with my desired outputs as explained above. You guys are unsung heroes. I will try and contribute as much to this page as possible, because my new job requires so much Excel hopefully I can learn it a lot and help other people on here.