# SolvedHelp - Excel Date Functions driving me crazy!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### Gevans0666

I have a contract start date and end date... dates are all ranges from 2019 to 2021 and longer

I have 4 columns for:
2018
2019
2020
Balance

I am trying to get a count of the months that contract are/were active based on 2 dates (start and finish). Seemed simple.... but not so much!

#### Attachments

• 12.3 KB Views: 6

#### Chawbacon

Hello again,

I am not sure how to pull today's date dynamically within a formula for calculation; however, if you want to use a control column that adds the current date for each row; such as, Column H values =Today() . You could then modify your existing formula to make the calculation; although, the calculation appears to have a bit of variance. Regardless, this should pull the number of months left on the contract.

Example =IF(H2="",0,DATEDIF(H2,G2,"m"))+1

Not an ideal solution; but, functional. Hopefully someone else on the forum has a better insight on dynamic date functions.

Good Luck.

#### cwwozniak

Spam Fighter
This should give you the total months between the start and end dates as well as the number of active months in each year (rounded to nearest whole numbers).

#### Attachments

• 42 KB Views: 5

#### Chawbacon

Oops! Just noticed that you have some start dates that are in the future. Updated my formula to accommodate for that oversight.

=IF(F2>H2,A2,IF(H2>G2,0,(DATEDIF(H2,G2,"m"))+1)) Where H is a control column =TODAY()

#### Gevans0666

This should give you the total months between the start and end dates as well as the number of active months in each year (rounded to nearest whole numbers).
This did it!!!!! OMG - this has been driving me nuts for weeks! Thanks!!

#### cwwozniak

Spam Fighter
You're welcome.

I hardcoded the years in the formulas for each column. The sheet can be edited so that one needs to only enter the earliest year (2018 in this case) in cell B1 and everything updates automatically.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On