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.

Effective Month/Date Calc - multiple years

Discussion in 'Business Applications' started by Gevans0666, Apr 30, 2019.

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

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Okay - so for the most part this is working - thanks to you, but I have run into a slight issue recently

    Issue: 2 dates where both "Contract Start Date" equals "Contract Expiry Date"
    Result should be "1" (as the contract while only 1 day was effective within 1 month)

    I appear to have figured out columns A & B by using MAX(0, but unsure if there is a more effective alternative?

    Current Formula for the 4 columns are:

    Col C Previous to this year
    =IF(OR(J16<DATE(2018,1,1),G16>DATE(2018,12,31)),0,DATEDIF(IF(G16<DATE(2018,1,1),DATE(2018,1,1),G16),IF(J16>DATE(2018,12,31),DATE(2018,12,31),J16)+15,"m"))

    Col D current year
    =IF(OR(J16<DATE(2019,1,1),G16>DATE(2019,12,31)),0,DATEDIF(IF(G16<DATE(2019,1,1),DATE(2019,1,1),G16),IF(J16>DATE(2019,12,31),DATE(2019,12,31),J16)+15,"m"))

    Col E Next Year
    =IF(OR(J16<DATE(2020,1,1),G16>DATE(2020,12,31)),0,DATEDIF(IF(G16<DATE(2020,1,1),DATE(2020,1,1),G16),IF(J16>DATE(2020,12,31),DATE(2020,12,31),J16)+15,"m"))

    Col F Balance
    =IF(J16<DATE(2021,1,1),0,DATEDIF(IF(G16<DATE(2021,1,1),DATE(2021,1,1),G16),J16+15,"m"))
     

    Attached Files:

  2. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    381
    First Name:
    Jack
    Hello again!

    You may want to consider turning the formula into a Nested IF statement; such as:

    For Column C
    =IF((G16=J16),1,(IF(OR(J16<DATE(2018,1,1),G16>DATE(2018,12,31)),0,DATEDIF(IF(G16<DATE(2018,1,1),DATE(2018,1,1),G16),IF(J16>DATE(2018,12,31),DATE(2018,12,31),J16)+15,"m"))))

    I hope this helps,

     
  3. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Almost - but in a further calculation to breakdown by month, it doesn't recognize the "1":

    =IFERROR(IF(AND(G16<=DATE(2019,1,1),J16>=DATE(2019,1,1)),K16/D16),0)
     

    Attached Files:

  4. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    381
    First Name:
    Jack
    Hello again,

    My apologies for the slow response; however, I have been unavailable due to multiple work projects.

    As for the formula. The "1" should be recognized; however, the formula for the "L" column is testing for a specific date range. When there is not a date present, or when the dates do not meet the predefined range, the "False" message will be displayed.

    You can test for a a blank cell by using the ISBlank option; however, this will still result in "False" values where the the dates do not meet the predefined range.

    =IF(ISBLANK(J16),0,IFERROR(IF(AND(G16<=DATE(2019,1,1),J16>=DATE(2019,1,1)),K16/D16),0))

    Are you looking for a "Zero" value when the dates do not meet the indicated range criteria?
     
  5. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
  6. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    =IF((G16=J16),1,(IF(OR(J16<DATE(2018,1,1),G16>DATE(2018,12,31)),0,DATEDIF(IF(G16<DATE(2018,1,1),DATE(2018,1,1),G16),IF(J16>DATE(2018,12,31),DATE(2018,12,31),J16)+15,"m"))))
    The above is now giving the "1" but on ALL years - not just 2018


    =IF(ISBLANK(J16),0,IFERROR(IF(AND(G16<=DATE(2019,1,1),J16>=DATE(2019,1,1)),K16/D16),0))

    The above works great and the zero result would be nice (aesthetically) but I can live without the zero - and it to be FALSE if I had to
     
  7. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    To clarify, the most important one is this one as it drives everything else... how many months are active (in this fiscal year) between 2 dates:

    =IF((G16=J16),1,(IF(OR(J16<DATE(2019,1,1),G16>DATE(2019,12,31)),0,DATEDIF(IF(G16<DATE(2019,1,1),DATE(2019,1,1),G16),IF(J16>DATE(2019,12,31),DATE(20189,12,31),J16)+15,"m")))

    Some Dates cover months: 01 Jan 2019 to 31 Mar 2019 (results in 3)

    Some cover only 1 day but need to be counted as an active month: 01 Jan 2019 to 01 Jan 2019 (results in 1)

    Some will cover other years not current and therefore not active this current fiscal. 01 Jan 2021 to 01 Jan 2021 (results in 0)


    Currently - it's doing what I want - except for the out years ... it's counting 1's for anything after 31 Dec 2019

    I hope I've explained correctly

    G

     
  8. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Here is the file with an example
     

    Attached Files:

  9. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    @Chawbacon ... also - no apologies for 'slow response" - I appreciate each and every effort you guys make!
     
  10. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    381
    First Name:
    Jack
    Alrighty! I had to look at this cross-eyed for a few minutes. :)

    Try modifying the formula for the "Effective Current Fiscal Year2" column (D) to check the year value within the "Calc Date" column (G), and return a value of zero if the year is greater than 2019:

    =IF(YEAR(G18)>2019,0,IF(G18=J18,1,IF(OR(J18<DATE(2019,1,1),G18>DATE(2019,12,31)),0,DATEDIF(IF(G18<DATE(2019,1,1),DATE(2019,1,1),G18),IF(J18>DATE(2019,12,31),DATE(2019,12,31),J18)+15,"m"))))


    I will have to ponder on the Months formula a bit. Is the IFERROR function a necessity for that column, or could a simple IF statement be used? I am thinking something along the lines of:

    =IF(AND($G19<=DATE(2019,3,1),$J19>=DATE(2019,3,1)),$K19/$D19,IF(D19=0,0,IF(ISBLANK($J19),0,$K19/$D19)))

    Oh... If you want to display zeros in the months columns, you will have to change the cell format to something other than "Accounting"... or force the formula to display text by quoting the return; but, this could cause challenges if you try to make calculations based upon the cell containing the text value. ;)

    =IF(AND($G19<=DATE(2019,3,1),$J19>=DATE(2019,3,1)),$K19/$D19,IF(D19=0,"0",IF(ISBLANK($J19),"0",$K19/$D19)))
     
  11. 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/1226477

  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