Share via

How do I calculate number of months between dates within IF statement?

Anonymous
2019-03-21T20:52:24+00:00

Hi,

I need to calculate the number of months between two dates.  The beginning date will either be on the 1st or 16th of the month.  If the 16th, I want it to calculate 1/2 month.  

Example:

Start Date:  5/16/19

End Date:  12/31/19

Number of months = 7.5 months

Need a formula to calculate the number of months between dates with an IF statement to determine if there are 1/2 months.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

30 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-03-21T21:42:51+00:00

    Hi Catwoman

    Please check below picture with a possible solution to your question.

    If this answer would be the solution to your question, I'll appreciate you mark it as answered

    Do let me know if you require any further help on this. Will be glad to help you.

    Thanks

    Regards

    Jeovany CV

    11 people found this answer helpful.
    0 comments
  2. Anonymous
    2019-03-21T22:25:08+00:00

    I have to use an IF statement as required by instructor

    0 comments
  3. Anonymous
    2019-03-22T00:47:11+00:00

    Hi Catwoman0828,

    My name is Dmitriy, I am an independent advisor.

    To Calculate the number of months between two dates you can use =datedif (starting date value, ending date value, "m") ("m" means month in here).

    If you need calculate half of the month I suggest you use the same formula

    =(datedif(starting date value, ending date value, "d")/15) --> which means that it will calculate days, and divide it by half a month.

    I hope it helps.

    Have a great day!

    6 people found this answer helpful.
    0 comments
  4. Anonymous
    2019-03-22T01:45:53+00:00

    Thanks to both of you.  Unfortunately, neither option gives me the answer I need.  The requirements are that I use an IF statement to calculate the number of months (to the half month) between dates.  I need to know the number of months only (not days).

    The IF statement must contain "-0.5" to calculate the half month if the "day" part of date is the 16th. 

    So, using my example, it must provide the # of months between 5/16/19 and 12/31/19 = 7.5

    5/1/19 - 12/31/19 = 8 months

    6/16/19 - 12/31/19 = 6.5 months

    0 comments
  5. Andreas Killer 144K Reputation points Volunteer Moderator
    2019-03-22T08:10:53+00:00

    There is no need for an IF, you can calcluate the result directly, see screenshot below.

    C1: =YEARFRAC(A1,B1)*12

    and drag down.

    If the days are not exactly at the half of a month (as you can see in row 4) you can round the result to the nearest of a half or full month:

    D4: =MROUND(YEARFRAC(A4,B4)*12,0.5)

    Andreas.

    6 people found this answer helpful.
    0 comments