# Difficult between dates formula needed

#### TerminatorX

##### New Member
Hi,

I need a (for me) difficult set of formula's to make a calculation.
I need the
- full months
- full workweeks
- and remaining workdays before and/or after the workweeks-months
to make a calculation.

An example:
Date 1: 16 November 2016
Date 2: 21 February 2017

The required output, in different cells:
1) workdays until next Monday as from date 1: 16 to 18 November = 3 days

2) full workweeks until the end of the Month of date 1: Week 21 - 25 November = 1 week
3) remaining workdays until the end of Month date 1: 28 until 30 November = 3 days

4) next full Months: December & January = 2 months

5) workdays from beginning of Month Date 2 until next Monday: 1 to 3 February = 3 days
6) full workweeks until date 2: 6 to 17 February = 2 weeks
7) remaining workdays to Date 2: 21 and 21 February = 2 days

There is no holiday calculation involved.
This goes beyond my knowledge. I hope some of the experts I have seen here are able to throw in some Date, Ceiling or Weekday voodoo to help me out. Thanks!

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### DRSteele

##### Well-known Member
Welcome to the forum.

I think I achieved your objective. I performed the calculations on each day of November and February to conduct experiments but I only include a few sample rows here so that we don't clutter the thread.

ABCDEFG
1DateThe next Monday(1) WorkdaysEnd of Month(2) Full Workweeks(3) Stub Workdays(4) Next two full months
2Tue, November 1, 2016Mon, November 7, 20164Wed, November 30, 201633November, December
17Wed, November 16, 2016Mon, November 21, 20163Wed, November 30, 201613December, January
31Wed, November 30, 2016Mon, December 5, 20163Wed, November 30, 201603December, January
32
33
34DateBeginning of monthThe next Monday(5) Workdays(6) Full Workweeks(7) Stub Workdays
35Wed, February 1, 2017Wed, February 1, 2017Mon, February 6, 2017303
55Tue, February 21, 2017Wed, February 1, 2017Mon, February 6, 2017322
62Tue, February 28, 2017Wed, February 1, 2017Mon, February 6, 2017332

</tbody>
Sheet8

Worksheet Formulas
CellFormula
B2=A2+LOOKUP(WEEKDAY(A2,2),{1;2;3;4;5;6;7},{7;6;5;4;3;2;1})
C2=NETWORKDAYS.INTL(A2,B2)-1
D2=EOMONTH(A2,0)
E2=TRUNC((D2-IF(WEEKDAY(A2,2)=1,A2,B2))/7)
F2=IF(WEEKDAY(D2,2)<5,WEEKDAY(D2,2),0)
G2=TEXT(IF(DAY(A2)=1,EDATE(A2,0),EDATE(A2,1)),"mmmm")&", "&TEXT(IF(DAY(A2)=1,EDATE(A2,1),EDATE(A2,2)),"mmmm")
B35=EOMONTH(A35,-1)+1
C35=B35+LOOKUP(WEEKDAY(B35,2),{1;2;3;4;5;6;7},{7;6;5;4;3;2;1})
D35=NETWORKDAYS.INTL(B35,C35)-1
E35=TRUNC((IF(WEEKDAY(A35,2)>=5,A35+3,A35)-IF(WEEKDAY(B35,2)=1,B35,C35))/7)
F35=IF(WEEKDAY(A35,2)<5,WEEKDAY(A35,2),0)

</tbody>

<tbody>
</tbody>

#### TerminatorX

##### New Member
Thanks, DRSteele!
This is fabulous.
Can you please rewrite G2, so it displays the number of full months? Could be two, could also be more, depending on the dates.

#### TerminatorX

##### New Member
Hi, and in your example november 30 to february 28, the output should be 3 months (nr4), 0 weeks (nr2 + 6) and 1 day (nov 30th; nr 1 and 5))
December 1st until februari 28, is 3 months, 0 weeks and o days, etc.
Is that doable?

#### TerminatorX

##### New Member
Thanks, DRSteele!
This is fabulous.
Can you please rewrite G2, so it displays the number of full months? Could be two, could also be more, depending on the dates.
Ok, formula of this one should be =DATEDIF(a2;a35;"m"), correct?

#### Rick Rothstein

##### MrExcel MVP
Can you please rewrite G2, so it displays the number of full months?
Just jumping in here to comment...

Full months are a terrible way to report differences in days because the length of months vary. To help us know how you measure them, what would the answer be for these date ranges...

January 28, 2017 to February 28, 2017

January 29, 2017 to February 28, 2017

January 30, 2017 to February 28, 2017

January 31, 2017 to February 28, 2017

February 28, 2017 to March 28, 2017

February 28, 2017 to March 29, 2017

February 28, 2017 to March 30, 2017

February 28, 2017 to March 31, 2017

#### TerminatorX

##### New Member
Thanks for stepping in, Rick. But I think I have solved it. There was also a small error in the C35 formula: when the first day of the month is on a Monday it calculates 5 weekdays ánd the weeks.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 Startdate Einddate The next Monday (1) Workdays begin of month End of Month (2) Full Workweeks (3) Stub Workdays (4) Next full months Begin of month End of Month The next Monday (5) Workdays (6) Full Workweeks (7) Stub Workdays 01-01-17 18-3-2017 02-01-17 0 1-1-2017 31-1-2017 0 0 2 1-3-2017 31-3-2017 6-3-2017 3 2 0 01-01-17 31-12-2017 02-01-17 0 1-1-2017 31-1-2017 0 0 12 1-12-2017 31-12-2017 4-12-2017 0 0 0 07-12-2016 6-6-2017 12-12-2016 3 1-12-2016 31-12-2016 2 0 6 1-6-2017 30-6-2017 5-6-2017 2 0 2 21-11-2016 30-4-2017 28-11-2016 5 1-11-2016 30-11-2016 1 3 5 1-4-2017 30-4-2017 3-4-2017 0 0 0 1-1-2017 31-1-2017 2-1-2017 0 1-1-2017 31-1-2017 0 0 1 1-1-2017 31-1-2017 2-1-2017 0 0 0 02-01-17 31-12-2017 09-01-17 5 1-1-2017 31-1-2017 4 2 11 1-12-2017 31-12-2017 4-12-2017 0 0 0 10-01-17 25-5-2017 16-01-17 4 1-1-2017 31-1-2017 2 2 4 1-5-2017 31-5-2017 8-5-2017 0 3 4

<colgroup><col style="width: 135px"><col width="95"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

The formula's:
c2: =A2+LOOKUP(WEEKDAY(A2;2);{1;2;3;4;5;6;7};{7;6;5;4;3;2;1})
d2:
=if(or(A2=E2;A2=F2);0;NETWORKDAYS.INTL(A2;C2)-1)
e2:
=EOMONTH(A2;-1)+1
f2:
=EOMONTH(A2;0)
g2:
=if(or(A2=E2;A2=F2);0;TRUNC((F2-IF(WEEKDAY(A2;2)=1;A2;C2))/7))
h2: =if(or(A2=E2;A2=F2);0;IF(WEEKDAY(F2;2)<5;WEEKDAY(F2;2);0))
i2:
=DATEDIF(A2;B2+1;"m")
j2:
=EOMONTH(B2;-1)+1
k2:
=EOMONTH(B2;0)
l2:
=J2+LOOKUP(WEEKDAY(J2;2);{1;2;3;4;5;6;7};{7;6;5;4;3;2;1})
m2:
=if(or(B2=J2;B2=K2);0;if(weekday(J2)=2;0;NETWORKDAYS.INTL(J2;L2)-1))
n2:
=if(or(B2=J2;B2=K2);0;TRUNC((IF(WEEKDAY(B2;2)>=5;B2+3;B2)-IF(WEEKDAY(J2;2)=1;J2;L2))/7))
o2:
=if(or(B2=J2;B2=K2);0;IF(WEEKDAY(B2;2)<5;WEEKDAY(B2;2);0))

#### Rick Rothstein

##### MrExcel MVP
Thanks for stepping in, Rick. But I think I have solved it.
I still think the questions I asked in Message #6 need to be answered by the OP before we can know if it is solved or not.

#### TerminatorX

##### New Member
d2 also showed workdays when date was almost end of the month (eg. nov 30) or showed 5 when A2 was on a Monday

=if(weekday(A2)=2;0;if(C2>F2+1;0;if(or(A2=E2;A2=F2);0;NETWORKDAYS.INTL(A2;C2)-1)))

#### TerminatorX

##### New Member
Dear Rick, please find the answers below. nr corresponds with 1/7 from post 1. When no nr is given, is is 0.

January 28, 2017 to February 28, 2017 : 2 workdays (nr1), 1 month (nr 4)

January 29, 2017 to February 28, 2017 : 2 workdays (nr1), 1 month (nr 4)

January 30, 2017 to February 28, 2017: : 2 workdays (nr1), 1 month (nr 4)

January 31, 2017 to February 28, 2017 : 1 workday (nr1), 1 month (nr 4)

February 28, 2017 to March 28, 2017 : 1 workday (nr1), 3 workdays (nr5), 3 workweeks (nr6), 2 workdays (nr7)

February 28, 2017 to March 29, 2017 : 1 workday (nr1), 3 workdays (nr5), 3 workweeks (nr6), 3 workdays (nr7)

February 28, 2017 to March 30, 2017 : 1 workday (nr1), 3 workdays (nr5), 3 workweeks (nr6), 4 workdays (nr7)

February 28, 2017 to March 31, 2017 : 1 workday (nr1), 1 month (nr4)

Replies
4
Views
452
Replies
9
Views
451
Replies
0
Views
180
Replies
1
Views
279
Replies
4
Views
480

1,191,191
Messages
5,985,211
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back