Your question, your audience. Choose who sees your identity—and your question—with question security.

Dear experts,

I need a simple formula which can calculate the number of months between two dates. I have provided a formula which I picked it up from website and I wish to perfect it to:

consider the holiday if the later date is a holiday, then the formula should consider the day after the holiday and then include it as the month

The list of holidays is provided in sheet 'holidays'

Column F has the desired result.

Kindly help.

Thank you

--of-months-between-two-dates.xlsx

I need a simple formula which can calculate the number of months between two dates. I have provided a formula which I picked it up from website and I wish to perfect it to:

consider the holiday if the later date is a holiday, then the formula should consider the day after the holiday and then include it as the month

The list of holidays is provided in sheet 'holidays'

Column F has the desired result.

Kindly help.

Thank you

--of-months-between-two-dates.xlsx

Can you please help me understand the logic behind -1 inside the brackets and +1 outside the brackets.

Thank you

that makes

a difference of one month between may 1, 16 to June 1, 16

plus 1 Equals 2

DATEDIF doesn't take into account holidays.

NETWORKDAYS can take weekends and a list of holidays into account but that gives number of working days rather than months.

Thanks

Rob

Formula in D9:

=IF(ISERROR(MATCH(C9,Holid

Then couple of options for column F:

=ROUNDUP(YEARFRAC(B9,IF(D9

=DATEDIF(B9,IF(D9="",C9-1,

Also some questions:

1) What if Start Date is a holiday?

2) What if Finish Date is a holiday and Finish Date +1 is also a holiday?

Also some questions:

1) What if Start Date is a holiday? Very unlikely

2) What if Finish Date is a holiday and Finish Date +1 is also a holiday? //Then move the date to the next working day but the Month count should still be in the previous month count or in the 1 month count (the floor value for the result)

```
WORKDAY.INTL(D9,-1,MOD(WEEKDAY(D9)+1,7)+1,Holidays!$A$2:$A$20))
```

calculates the first non holiday date before D9 this part

```
MOD(WEEKDAY(D9)+1,7)+1
```

place the weekend after D9 to not affect the calculation of the non holiday date
Values in Col. B and C are given and I cannot change them

Values in Col D are only shown for this illustration to convey my thought process. In the actual file this field would not exist.

Thank you

The number of months should be calculated based on values in col. b and C and column D would not exist in the actual file.

Kindly help.

Thank you

The idea is to get a formula to do the following:

1. check if the value in tab Examples, col C will coincide with the list in tab Holidays, Col A

2. Then the formula should increment the value in tab Examples, col C by one date

3. Further, the formula should check if the value arrived in step 2 above is working day or a weekend (Saturday or Sunday)

4. If the result of step 4 coincides with weekend, then the formula should increment it by 1 (if sunday)or 2 (if Saturday) to arrive at the next working day Monday.

5. if the result of Step 4 is Monday and a holiday (as per list), then it should increment by 1 day again to result in Tuesday.

Now suppose if as per the default calculation col. c less Col. B results in 2 months,

then by following step 2-5 should also result in 2 months and not 3 months.

Sorry if I have confused the forum and I hope I am clear with my request now.

Kindly help

If it does not change the value of the result at the end, why does the formula have to know there is a weekend or a holiday?

How can single or multiple holiday days have an impact on the result? For lapsed months the only way that holidays will have an impact is when you assign a whole month as a holiday.

Thanks

Rob

All Courses

From novice to tech pro — start learning today.

pls try

Open in new window

--of-months-between-two-datesV1.xlsx