1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Help - Excel Date Functions driving me crazy!

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

Thread Status:
Not open for further replies.
Advertisement
  1. Gevans0666

    Gevans0666 Thread Starter

    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!
     

    Attached Files:

  2. Chawbacon

    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.
     
  3. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    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).
     

    Attached Files:

  4. Chawbacon

    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()
     
  5. Gevans0666

    Gevans0666 Thread Starter

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

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    60,866
    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.
     
    Chawbacon likes this.
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1222108

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice