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.

Excel Date Formula to determine active months within year

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

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

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Attached is a excel sheet where I've made 8 date calculations...

    Te issue is in column E =IF(I2="",0,MIN(12,(DAYS360(H2,$O$1)/30-D2)))

    Some contracts start in 2019 while others end in 2019... I am trying to count which months contract will be effective... what am I missing? Do I need an additional column or can this be accomplished in one formula?

    Thanks
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    can you give some examples where the formula is wrong and what answer you need
    for example
    row 15 is 9 months
    because it starts on 31st March - did you want to count 10 months?

    there is also
    =DATEDIF(start_date,end_date,"m")
    =DATEDIF(H2,$O$1,"m")
    will give you the number of months between dates - rounds down

    but row 73 - only complete months

    if you want to nearest month
    =MIN(12,DATEDIF(H2,$O$1+15,"m")) rounds up

    see here
    https://exceljet.net/formula/get-months-between-dates
     
  3. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    For example:

    Line 33 should read 4 months (starts in March and ends in June of 2019)

    Line 40 should read 9 months (effective for first 9 months of 2019)
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    why are using the end date as 31/12/19 in cell O2
    and not the column K?

    =IF(OR(I2="",K2<DATEVALUE("1/1/19")),0,MIN(12,DATEDIF(H2,K2+15,"m")-D2))
     
  5. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Oh wow... I NEVER would have figured that formula out! Works like a charm... thanks :)
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    you are welcome
     
  7. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Okay - so having issue with this... it is incorrectly calculating those records that start partway through 2019 and run past 2019 (could be 202 up to 2029)... to a max of 12 for the current year.
     

    Attached Files:

  8. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    ....same with those records that start prior to 2019 and end partway in 2019
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    can you give some row numbers and what you want to see
    I started looking into it and was a little confused
     
  10. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    Here it is....
     

    Attached Files:

  11. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    I was wondering if you could have a look at the formula I am using in M3... it's a calculation to spread an amount evenly over 12 months based on a date range -- while it works - it is always 1 month ahead.. meaning if the project starts in Oct the savings show up in Nov (but I want them actually showing in Oct)... what am I missing here?
     
  12. Gevans0666

    Gevans0666 Thread Starter

    Joined:
    Sep 6, 2018
    Messages:
    40
    In case I didn't explain it correctly -- I am trying to determine the 'active' months within a date range (specifically 01 Jan 2019 to 31 Dec 2019). Some records start prior to Jan 01 and end in 2019... some don't tart until midway through 2019 and end in 2022 or further.
     
  13. 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/1221827

  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