need help with calculating a date?

PROV226RV

New Member
Joined
Apr 1, 2013
Messages
31
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 2014February 2014March 2014
SuMTuWThFSaSuMTuWThFSaSuMTuWThFSa
1234 1 1
56789101123456782345678
1213141516171891011121314159101112131415
192021222324251617181920212216171819202122
262728293031 232425262728 23242526272829
3031
April 2014May 2014June 2014
SuMTuWThFSaSuMTuWThFSaSuMTuWThFSa
12345 1231234567
678910111245678910891011121314
131415161718191112131415161715161718192021
202122232425261819202122232422232425262728
27282930 252627282930312930
July 2014August 2014September 2014
SuMTuWThFSaSuMTuWThFSaSuMTuWThFSa
12345 12 123456
6789101112345678978910111213
131415161718191011121314151614151617181920
202122232425261718192021222321222324252627
2728293031 24252627282930282930
31
October 2014November 2014December 2014
SuMTuWThFSaSuMTuWThFSaSuMTuWThFSa
1234 1 123456
567891011234567878910111213
12131415161718910111213141514151617181920
192021222324251617181920212221222324252627
262728293031 2324252627282928293031
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
Jan-14
SuMTuWThFSa
1234
567891011
12131415161718
19202122232425
262728293031
all you need is min(f4:f9)

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

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
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
Joined
Apr 1, 2013
Messages
31
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
Joined
Apr 1, 2013
Messages
31

ADVERTISEMENT

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
Joined
Apr 11, 2010
Messages
11,005
are you paid weekly?

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

PROV226RV

New Member
Joined
Apr 1, 2013
Messages
31
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
 

Forum statistics

Threads
1,137,294
Messages
5,680,654
Members
419,923
Latest member
Kalthus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top