Hi, Thank you for clicking. This is my first post on this sub and I come with something that I for the life of me cannot figure out. I am working on a project where I need to calculate the amount of days impacted in prior months for backdated 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 month after all contracts have been invoiced. To put it in perspective, I work for an equipment rental company, so if a contract is written on 6/1 but was dated for 5/28, the accounting department needs to know to add 3 days rent on the prior month. I need to write a formula which will capture the following in a cell:
-If the current date is 6/1 and a contract was dated for 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 for a start date of 6/1, the cell should say "0" because the prior month was not impacted.
-If a contract was written on 6/1 for 4/1, the cell should say "61" since May and April were impacted.
Any help is greatly appreciated. I am a recent college graduate and will be using Excel a lot in my current career. I promise to come back and contribute as my Excel knowledge advances. Thank you all.
-If a contract was written on 6/5 for 4/1, the cell should still say 61 since only 2 previous closed months were impacted.
I have already tried the formula: =IF(MONTH(A5)>MONTH(B5),A5-B5,0). The problem is it doesn't account for my last example, where it should subtract the number of days in the current month (6/June). There is an attachment with what I need on here.
Any help is greatly appreciated. I am a recent college graduate and will be using Excel a lot in my current career. I promise to come back and contribute as my Excel knowledge advances. Thank you all.
Bookmarks