Solved Help - 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

Thread Starter
Joined
Sep 6, 2018
Messages
41
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

Joined
Jul 9, 2018
Messages
561
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.
 

cwwozniak

Trusted Advisor
Spam Fighter
Joined
Nov 28, 2005
Messages
65,092
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).
 

Attachments

Joined
Jul 9, 2018
Messages
561
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()
 

Gevans0666

Thread Starter
Joined
Sep 6, 2018
Messages
41
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

Trusted Advisor
Spam Fighter
Joined
Nov 28, 2005
Messages
65,092
First Name
Chuck
You're welcome. (y)

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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top