Difficult between dates formula needed

[TerminatorX]

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!

 

[DRSteele]

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.

	A	B	C	D	E	F	G
1	Date	The next Monday	(1) Workdays	End of Month	(2) Full Workweeks	(3) Stub Workdays	(4) Next two full months
2	Tue, November 1, 2016	Mon, November 7, 2016	4	Wed, November 30, 2016	3	3	November, December
17	Wed, November 16, 2016	Mon, November 21, 2016	3	Wed, November 30, 2016	1	3	December, January
31	Wed, November 30, 2016	Mon, December 5, 2016	3	Wed, November 30, 2016	0	3	December, January
32
33
34	Date	Beginning of month	The next Monday	(5) Workdays	(6) Full Workweeks	(7) Stub Workdays
35	Wed, February 1, 2017	Wed, February 1, 2017	Mon, February 6, 2017	3	0	3
55	Tue, February 21, 2017	Wed, February 1, 2017	Mon, February 6, 2017	3	2	2
62	Tue, February 28, 2017	Wed, February 1, 2017	Mon, February 6, 2017	3	3	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[TerminatorX]

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]

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]

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]

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]

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]

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]

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]

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)