Auto populate months

[keef2]

Hi there,

So I already auto populate months based on my choose formula in Row 2, but I was hoping someone maybe able to help me out with a different approach. Looking to show split months if that makes sense. So if a week falls within 2 months. Out put would be Nov/Dec, Jan/Feb. Otherwise if all dates are within a month would just revert to the current month if that makes sense. Any help is always appreciated, thanks in advance!

SRI Schedule Prototype.xlsx
	A	B	C	D	E	F			I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1													32	24	24	24	16	16	16	16	16	16	24	24	24	24	24	24	32	32	32	32		32	32	32	32		40	40	40	40	40	40	40	40	40	40	40	40	40	40	48	40	40	40	40	40	40	48	48	40
2									Contract	Submittals	Labor Req.		Nov					Dec					Dec					Dec					Dec					Jan					Jan					Jan					Jan					Feb
3	Job #	Job Name	Roof System	PM	Supt	Start Date						Contractor	28	29	30	1	2	5	6	7	8	9	12	13	14	15	16	19	20	21	22	23	26	27	28	29	30	2	3	4	5	6	9	10	11	12	13	16	17	18	19	20	23	24	25	26	27	30	31	1	2	3
Schedule

Cell Formulas
Range	Formula
M1:BJ1	M1	=IF(SUM(M4:M32)=0,"",SUM(M4:M32))
M2,R2,W2,AB2,AG2,AL2,AQ2,AV2,BA2,BF2	M2	=CHOOSE(MONTH(O3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
M3	M3	=WORKDAY.INTL(C35+1,-1,"0111111")
N3:BJ3	N3	=WORKDAY(M3,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M3:ADL32,M37:ADL42	Expression	=COUNTIFS(Holidays!$F$3:$F$68,M$3)	text	NO
I1,I4:I32,I35:I1048576	Expression	=AND(NOT(ISBLANK($A1)),$I1="N")	text	NO
I1,I4:I32,I35:I1048576	Expression	=AND(NOT(ISBLANK($A1)),$I1="Y")	text	NO
J1,J35:J1048576,J4:J32	Expression	=AND(NOT(ISBLANK($A1)),$J1="S")	text	NO
J1,J35:J1048576,J4:J32	Expression	=AND(NOT(ISBLANK($A1)),$J1="NS")	text	NO
J1,J35:J1048576,J4:J32	Expression	=AND(NOT(ISBLANK($A1)),$J1="A")	text	NO
I2	Expression	=AND(NOT(ISBLANK($A3)),$I2="N")	text	NO
I2	Expression	=AND(NOT(ISBLANK($A3)),$I2="Y")	text	NO
J2	Expression	=AND(NOT(ISBLANK($A3)),$J2="S")	text	NO
J2	Expression	=AND(NOT(ISBLANK($A3)),$J2="NS")	text	NO
J2	Expression	=AND(NOT(ISBLANK($A3)),$J2="A")	text	NO

 

[Jeffrey Mahoney]

Please try this formula in M2
=IF(MONTH(M3)<>MONTH(Q3),TEXT(M3,"mmm")&"/"&TEXT(Q3,"mmm"),TEXT(M3,"mmm"))

 

[keef2]

Please try this formula in M2
=IF(MONTH(M3)<>MONTH(Q3),TEXT(M3,"mmm")&"/"&TEXT(Q3,"mmm"),TEXT(M3,"mmm"))

works like a charm! thank you much! never would have thought to do that.

SRI Schedule Prototype.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1								Duration					24	24	24	24	16	16	16	16	16	16	32	24	16	24	24	24	24	32	32	32		32	32	32	32		40	40	40	40	40	40	40	40	40	40	40	40	40	40	48	48	40	40	40	40	40	48	48	40
2									Contract	Submittals	Labor Req.		Nov / Dec					Dec					Dec					Dec					Dec					Jan					Jan					Jan					Jan					Jan / Feb
3	Job #	Job Name	Roof System	PM	Supt	Start Date	End Date					Contractor	28	29	30	1	2	5	6	7	8	9	12	13	14	15	16	19	20	21	22	23	26	27	28	29	30	2	3	4	5	6	9	10	11	12	13	16	17	18	19	20	23	24	25	26	27	30	31	1	2	3
4		Javier Rodriguez
Roofing Schedule

Cell Formulas
Range	Formula
M1:BJ1	M1	=IF(SUM(M4:M32)=0,"",SUM(M4:M32))
M2,R2,W2,AB2,AG2,AL2,AQ2,AV2,BA2,BF2	M2	=IF(MONTH(M3)<>MONTH(Q3),TEXT(M3,"mmm")&" / "&TEXT(Q3,"mmm"),TEXT(M3,"mmm"))
N2:Q2,S2:V2,X2:AA2,AC2:AF2,AH2:AK2,AM2:AP2,AR2:AU2,AW2:AZ2,BB2:BE2,BG2:BJ2	N2	=IF(MONTH(U3)<>MONTH(Y3),TEXT(U3,"mmm")&"/"&TEXT(Y3,"mmm"),TEXT(U3,"mmm"))
M3	M3	=WORKDAY.INTL(C35+1,-1,"0111111")
N3:BJ3	N3	=WORKDAY(M3,1)
M4:BJ4	M4	=IF($G4="","",IF(AND(M$3>=$F4,NOT(COUNTIFS(Holidays!$F$3:$F$51,M$3)),M$3<=$G4),8,""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:H32,H35:H63	Expression	=AND(NOT(ISBLANK($F4)),ISBLANK($H4))	text	NO
M4:ADL32,M37:ADL42	Expression	=M$3=TODAY()	text	NO
M3:ADL32,M37:ADL42	Expression	=COUNTIFS(Holidays!$F$3:$F$68,M$3)	text	NO
A33:D34,L33:XFD34,F33:H34,37:62,4:32	Expression	=AND(NOT(ISBLANK($B4)),ISBLANK($A4))	text	NO
I1,I4:I32,I35:I1048576	Expression	=AND(NOT(ISBLANK($A1)),$I1="N")	text	NO
I1,I4:I32,I35:I1048576	Expression	=AND(NOT(ISBLANK($A1)),$I1="Y")	text	NO
J1,J4:J32,J35:J1048576	Expression	=AND(NOT(ISBLANK($A1)),$J1="S")	text	NO
J1,J4:J32,J35:J1048576	Expression	=AND(NOT(ISBLANK($A1)),$J1="NS")	text	NO
J1,J4:J32,J35:J1048576	Expression	=AND(NOT(ISBLANK($A1)),$J1="A")	text	NO
I2	Expression	=AND(NOT(ISBLANK($A3)),$I2="N")	text	NO
I2	Expression	=AND(NOT(ISBLANK($A3)),$I2="Y")	text	NO
J2	Expression	=AND(NOT(ISBLANK($A3)),$J2="S")	text	NO
J2	Expression	=AND(NOT(ISBLANK($A3)),$J2="NS")	text	NO
J2	Expression	=AND(NOT(ISBLANK($A3)),$J2="A")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I4:I32	List	=Holidays!$I$2:$I$3
J4:J32	List	=Holidays!$J$2:$J$4