Start & End date of a month year

[hsandeep]

How to get 1st date & last date of a month based on month punched in a cell?
Ex:
A1=JULY, B1=2013
Answer: C1=01July2013, D1=31July2013
Also, required 01July2013 in separate 8 (or required no.) of cells.
Also, required 31July2013 in separate 8 (or required no.) of cells.

 

[alansidman]

In C1 =DATE(B1,MONTH(DATEVALUE(A1& "1")),1)
In D1 =EOMONTH(C1,0)

 

[hsandeep]

Have you factored in: February month ending with 29?
It WORKS.

Now, please SEPARATE 01July2013 in different cells as:
E1=0, F1=1, G1=J, H1=U, I1=L, J1=Y, K1=2, L1=0, M1=1, N1=3

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	July	2013	Jul 01, 2013	Jul 31, 2013				End of Month
2	01July2013
3	0	1	J	u	l	y	2	0	1	3
4
3a

Excel 2003

Cell Formulas
Range	Formula
C1	=(A1&B1)+0
D1	=DATE(YEAR(C1),MONTH(C1)+1,0)
A2	=TEXT(C1,"ddmmmmyyyy")
A3	=MID($A$2,COLUMN(),1)
B3	=MID($A$2,COLUMN(),1)

 

[hsandeep]

3a[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]July[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]Jul 01, 2013[/TD]
[TD="align: right"]Jul 31, 2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]End of Month[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]01July2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]J[/TD]
[TD]u[/TD]
[TD]l[/TD]
[TD]y[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003

Cell	Formula
C1	=(A1&B1)+0
D1	=DATE(YEAR(C1),MONTH(C1)+1,0)
A2	=TEXT(C1,"ddmmmmyyyy")
A3	=MID($A$2,COLUMN(),1)
B3	=MID($A$2,COLUMN(),1)

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

Formula in A3 & B3 is not giving desired results. Please re-check.

 

[hsandeep]

Since no. of characters in months 'varies'. so output 01072013 is required instead of 01July2013. How to get 01072013 in above case?

 

[hsandeep]

Also, for A1=July & B1=2013
Answer K1=31 (last date) or
A1=September & B1=2013
Answer K1=30 (last date).
How to achieve?

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	September	2013	Sep 01, 2013	Sep 30, 2013				End of Month		Days	30
2	01092013	30092013
3	0	1	0	9	2	0	1	3
4	3	0	0	9	2	0	1	3
3a

Excel 2003

Cell Formulas
Range	Formula
C1	=(A1&B1)+0
D1	=DATE(YEAR(C1),MONTH(C1)+1,0)
K1	=DAY(D1)
A2	=TEXT(C1,"ddmmyyyy")
A3	=MID($A$2,COLUMN(),1)
A4	=MID($B$2,COLUMN(),1)
B2	=TEXT(D1,"ddmmyyyy")

Since no. of characters in months 'varies'. so output 01072013 is required instead of 01July2013. How to get 01072013 in above case?
Did you try editing the formula in A2?

Dave

 

[hsandeep]

No. Since, I don't know what to edit in the formula of A2 to get 01072013 instead of 01July2013.

 

[Dave Patton]

"edit in the formula of A2 to get 01072013 " see message #8 above.

If you do not want to put the text in cell A2, try the following and copy it across.

=MID(TEXT($D$1,"ddmmyyyy"),COLUMN(),1)