need help with calculating a date?

[PROV226RV]

I need to create a formula that will tell me when the first Thursday of the month is (that is payday). I have my calendar set up in Excel (this is for my personal budget) and I can "see" when the first Thursday of the month is and type it in the cell but I want a formula to dynamically update based on the current month.

So, if cell A3="JANUARY" and cell A4="2014" and the calendar is way below in cell C64 (which is the month name, row 65 = days of the week, and row 66 starts the actual calendar), how would I have it lookup the first instance of a Thursday (with a date) in that month (with January 2, 2014 being the first Thursday and sits in cell G66)? Each month has 6 rows and I have 3 months across with a blank column in between each month and a blank row in between each month. Calendar parameters are from C64 to Y98.

January 2014		February 2014		March 2014
Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa
			1	2	3	4								1								1
5	6	7	8	9	10	11		2	3	4	5	6	7	8		2	3	4	5	6	7	8
12	13	14	15	16	17	18		9	10	11	12	13	14	15		9	10	11	12	13	14	15
19	20	21	22	23	24	25		16	17	18	19	20	21	22		16	17	18	19	20	21	22
26	27	28	29	30	31			23	24	25	26	27	28			23	24	25	26	27	28	29
																30	31
April 2014		May 2014		June 2014
Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa
		1	2	3	4	5						1	2	3		1	2	3	4	5	6	7
6	7	8	9	10	11	12		4	5	6	7	8	9	10		8	9	10	11	12	13	14
13	14	15	16	17	18	19		11	12	13	14	15	16	17		15	16	17	18	19	20	21
20	21	22	23	24	25	26		18	19	20	21	22	23	24		22	23	24	25	26	27	28
27	28	29	30					25	26	27	28	29	30	31		29	30
July 2014		August 2014		September 2014
Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa
		1	2	3	4	5							1	2			1	2	3	4	5	6
6	7	8	9	10	11	12		3	4	5	6	7	8	9		7	8	9	10	11	12	13
13	14	15	16	17	18	19		10	11	12	13	14	15	16		14	15	16	17	18	19	20
20	21	22	23	24	25	26		17	18	19	20	21	22	23		21	22	23	24	25	26	27
27	28	29	30	31				24	25	26	27	28	29	30		28	29	30
								31
October 2014		November 2014		December 2014
Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa		Su	M	Tu	W	Th	F	Sa
			1	2	3	4								1			1	2	3	4	5	6
5	6	7	8	9	10	11		2	3	4	5	6	7	8		7	8	9	10	11	12	13
12	13	14	15	16	17	18		9	10	11	12	13	14	15		14	15	16	17	18	19	20
19	20	21	22	23	24	25		16	17	18	19	20	21	22		21	22	23	24	25	26	27
26	27	28	29	30	31			23	24	25	26	27	28	29		28	29	30	31
								30

<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"><col span="12" style="text-align: center;"><col span="6" style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

I also have another cell below the calendar that will allow me to change the payday from Thursday to another day in case I need to use a lookup or match formula. It is cell W102.


I have tried several different options but basically all my ideas need a starting date and I can't think clearly enough to do this. It is probably so simple but I can't see it.

Thanks!

Rachel V in Florida

 

[oldbrewer]

	Jan-14
	Su	M	Tu	W	Th	F	Sa
				1	2	3	4
	5	6	7	8	9	10	11
	12	13	14	15	16	17	18
	19	20	21	22	23	24	25
	26	27	28	29	30	31
all you need is min(f4:f9)

<colgroup><col span="9"></colgroup><tbody>
</tbody>

 

[BrianMH]

I did something quite similar to this recently. Instead of storing each day as a number I actually store it as it's correct date and then format it to "DD". Then I used conditional formatting to highlight paydays and holidays that appear in a list on another tab.

 

[PROV226RV]

that worked but I would still have to change it from month to month. And what if the payday changes from Thursday to another day? Is there a way to calculate this by looking at the payday in one cell? If not, I can work around it. Would like to make it simpler though...

 

[PROV226RV]

I do have conditional formatting for the paydays but I would like to see a list at the top of the page like so:

Jonathan - 2, 9, 16, 23, 30 Then for February it would be: Jonathan - 6. 13, 20, 27 and so on. Is this at all possible? I'm just a little anal about things like that LOL

 

[oldbrewer]

are you paid weekly?

if so you just need a list starting jan 2 increasing by 7 days

 

[PROV226RV]

are you paid weekly?

if so you just need a list starting jan 2 increasing by 7 days

Yes, weekly. But my husband is changing jobs soon and we don't know the new payday or how often yet. So I was wondering how to do what I was looking to do ...

I tried the MIN function and it works except that for each new month I have to change the formula. That is NOT a problem but i was curious if a formula could be done on such a large array of 12 months... I don't think it is possible .. even if I listed all the dates in 1 column going down consecutively.

Not a big deal. I will change the formula monthly on my budget!

Thanks for the help