need help with calculating a date?

PROV226RV

New Member
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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

oldbrewer

Well-known Member
 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

Well-known Member
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

New Member
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

New Member
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

Well-known Member
are you paid weekly?

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

PROV226RV

New Member
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

Replies
12
Views
575
Replies
9
Views
209
Replies
18
Views
667
Replies
15
Views
383
Replies
5
Views
194

1,171,683
Messages
5,876,884
Members
433,217
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back