# Solved Help - Excel Date Functions driving me crazy!

Discussion in 'Business Applications' started by Gevans0666, Jan 19, 2019.

Not open for further replies.

Joined:
Sep 6, 2018
Messages:
33
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!

File size:
12.3 KB
Views:
6
2. ### Chawbacon

Joined:
Jul 9, 2018
Messages:
310
First Name:
Jack
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.

Joined:
Nov 28, 2005
Messages:
60,866
First Name:
Chuck
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).

File size:
42 KB
Views:
5
4. ### Chawbacon

Joined:
Jul 9, 2018
Messages:
310
First Name:
Jack
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()

Joined:
Sep 6, 2018
Messages:
33
This did it!!!!! OMG - this has been driving me nuts for weeks! Thanks!!

Joined:
Nov 28, 2005
Messages:
60,866
First Name:
Chuck
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.

Chawbacon likes this.

As Seen On