Results 1 to 5 of 5

Thread: Calculating months if all days count (DATEDIF)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2003
    Location
    England
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculating months if all days count (DATEDIF)

    Hi

    I have an issue where I want to count complete months where all days count in the month.

    For example if you look at the difference between 2 dates, so 01/01/19 minus 01/01/19 we get 0, but say I want to say someone attended a course on that day, they in fact completed 1 day. So I want the formula to say 1. So I just add 1 in the formula.

    Using date dif function on months, if I use say 01/01/19 to 31/1/19 the formula returns 0. In practical terms if I say some attended on these dates, they have completed 1 month, not 'no months'

    How can I get a formula to give me the answer of 1 month completed in this scenario...

    As another example. so if it was across 01/01/19 - 28/02/19 I would get 2 months, where the formula now gives me 1 month.

    Thanks for all your help
    Chris

  2. #2
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    534
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating months if all days count (DATEDIF)

    I don’t know this formula very well, but it seems that someone else had a similar problem with inclusive dates. Perhaps this will help.

    https://www.mrexcel.com/forum/excel-...end-dates.html

  3. #3
    Board Regular
    Join Date
    Jul 2003
    Location
    England
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating months if all days count (DATEDIF)

    Hi. Thanks for this but this solution seems to just sort the days count which I am OK with, its the months

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,168
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculating months if all days count (DATEDIF)

    Maybe just subtract the months and add 1.

    ABC
    1Start DateEnd Date# Months
    21/2/20191/30/20191
    32/2/20194/5/20193
    42/2/20193/1/20192
    55/6/20195/20/20191

    Spreadsheet Formulas
    CellFormula
    C2=MONTH(B2)-MONTH(A2)+1


    Excel tables to the web >> Excel Jeanie HTML 4

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,168
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculating months if all days count (DATEDIF)

    The formula I gave above would only work if months are in the same year.
    Instead maybe:

    ABC
    1Start DateEnd DateMonths
    21/1/20191/3/20191
    31/8/20192/1/20192
    412/6/20182/1/20193
    512/4/20183/3/20194
    62/2/20196/4/20195
    710/5/20172/6/201917

    Spreadsheet Formulas
    CellFormula
    C2=DATEDIF(A2,EOMONTH(B2,0),"m")+1


    Excel tables to the web >> Excel Jeanie HTML 4

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •