Find last date of a month, given only year and month

[girapas]

In Sheet1 I enter a year and a month. Sheet2 is a database. Column A of Sheet2 filled with working dates. Among other things, I want to find the last date of the year's month entered in Sheet1, and get the cell value addressed of the column G and the same row with that last date.
Thanks in advance

 

[MrKowz]

In Sheet1 I enter a year and a month. Sheet2 is a database. Column A of Sheet2 filled with working dates. Among other things, I want to find the last date of the year's month entered in Sheet1, and get the cell value addressed of the column G and the same row with that last date.
Thanks in advance

Can you please post a sample of your data, and exactly how you enter the year and month? There are different approaches to this, based primarily on how the year/month is entered.

 

[girapas]

Here is a sample. In sheet2 I've put only one month.

b></b>

Book1
	A	B
1	enter year	2012
2	enter month	1
3
4	last day of month entered in B2, of year entered in B1	?
5	Total value (col G) of the last day of this month found	?
6
Sheet1

Book1
	A	B	C	D	E	F	G
1		A B C D E
2	date	AAA	BBB	CCC	DDD	EEE	Total
3	2/1/2012	86	99	97	64	33	379
4	3/1/2012	18	81	64	76	99	338
5	4/1/2012	22	61	27	26	69	205
6	5/1/2012	53	18	52	21	33	177
7	9/1/2012	44	75	86	55	56	316
8	10/1/2012	40	27	36	69	47	219
9	11/1/2012	29	64	95	16	80	284
10	12/1/2012	43	82	24	19	49	217
11	13/1/2012	66	37	57	75	64	299
12	16/1/2012	85	72	53	29	77	316
13	17/1/2012	42	78	24	35	18	197
14	18/1/2012	66	75	13	33	20	207
15	19/1/2012	61	26	97	73	47	304
16	20/1/2012	65	11	78	72	25	251
17	23/1/2012	72	54	91	49	40	306
18	24/1/2012	35	90	86	69	77	357
19	25/1/2012	63	20	72	21	30	206
20	26/1/2012	70	41	35	20	17	183
21	27/1/2012	60	69	70	78	68	345
22	30/1/2012	94	77	99	76	42	388
23	31/1/2012	18	81	69	71	83	322
24	etc.	etc.	etc.	etc.	etc.	etc.	etc.
25
Sheet2

 

[Rick Rothstein]

Here is a sample. In sheet2 I've put only one month.

b></STRONG>

	A	B

<TBODY>
[TD="align: center"]1
[/TD]
[TD="align: center"]enter year
[/TD]
[TD="align: center"]2012
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: center"]enter month
[/TD]
[TD="align: center"]1
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="align: center"]last day of month entered in B2, of year entered in B1
[/TD]
[TD="align: center"]?
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: center"]Total value (col G) of the last day of this month found
[/TD]
[TD="align: center"]?
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</TBODY>

Sheet1​

For the last day of the month, use this formula...

=DATE(B1,B2+1,0)

 

[Rick Rothstein]

And this formula should get you the Column G value for that date from the chart on Sheet1...

=LOOKUP(B4,Sheet1!A:A,Sheet1!G:G)

 

[girapas]

Thank you, it's so simple! But I'm afraid I wasn't clear about it. The dates in Sheet2 are working days. So the actual last day of a month doesn't always is the same with the working day calculated.

 

[Rick Rothstein]

Thank you, it's so simple! But I'm afraid I wasn't clear about it. The dates in Sheet2 are working days. So the actual last day of a month doesn't always is the same with the working day calculated.

Okay, the try this formula...

=WORKDAY(DATE(B1,B2+1,1),-1)

 

[Aladin Akyurek]

Thank you, it's so simple! But I'm afraid I wasn't clear about it. The dates in Sheet2 are working days. So the actual last day of a month doesn't always is the same with the working day calculated.

B4:

Either...

=WORKDAY(DATE(B1,B2+1,1),-1)

Or...

=DATE(B1,B2+1,1)-1

B5:

=VLOOKUP(B4,Sheet2!$A$2:$G$22,7,0)

Does the foregoing work for you?

 

[girapas]

Great! Thank you. I hope I'm not be demanding, but there's yet a slight problem. I've named public and local holidays as Holidays2012, Holidays2013 etc. to calculate the working dates. Is there any way to include that dates in the formula you gave me?

 

[Rick Rothstein]

Great! Thank you. I hope I'm not be demanding, but there's yet a slight problem. I've named public and local holidays as Holidays2012, Holidays2013 etc. to calculate the working dates. Is there any way to include that dates in the formula you gave me?

Assuming you mean Holidays2012, Holidays2013, etc. are Defined Names for ranges containing dates for the holidays for those years, you can use this modification to the formula and it will adjust automatically for the correct year (taking it from B1) so long as you have a Defined Name for your holidays for each you plan to use his worksheet...

=WORKDAY(DATE(B1,B2+1,1),-1,INDIRECT("Holidays"&B1))