Date Formula Help

[LearnVBA83]

Hi team excel,

I have a column in excel with Month names (Column B) and a column in Excel with years (Column C). Is there a formula that I can use to look at column B and C and return the last day of the month. For example in B2 I have January and in C2 I have 2006 is there a way to enter a formula in column D2 that returns 1/31/2006 in the actual date format?

 

[Joe4]

Try this formula:

=EOMONTH(DATEVALUE(B2&" 1, "&C2),0)

 

[felixstraube]

I think you can reduce the formula to this:

Book1.xlsx
	A	B	C	D	E
1
2		Jan	2022		1/31/2022
3		Feb	2023		2/28/2023
4		Mar	2024		3/31/2024
5		Apr	2025		4/30/2025
6		May	2026		5/31/2026
7		Jun	2027		6/30/2027
8		Jul	2028		7/31/2028
9		Aug	2029		8/31/2029
10		Sep	2030		9/30/2030
11		Oct	2031		10/31/2031
12		Nov	2032		11/30/2032
13		Dec	2033		12/31/2033
14		January	2034		1/31/2034
15		February	2035		2/28/2035
16		March	2036		3/31/2036
17		April	2037		4/30/2037
18		May	2038		5/31/2038
19		June	2039		6/30/2039
20		July	2040		7/31/2040
21		August	2041		8/31/2041
22		September	2042		9/30/2042
23		October	2043		10/31/2043
24		November	2044		11/30/2044
25		December	2045		12/31/2045
Sheet3

Cell Formulas
Range	Formula
E2:E25	E2	=EOMONTH(B2&C2, 0)

Not sure if it would work with any regional format, but I tried UK an US and it worked ok.