Date Formula Request

dbsbender

New Member
Joined
Aug 25, 2011
Messages
10
In advance - thank you for reading.

I am trying to calculate the "3rd Thursday of next quarter". Going forward, I'd like to be able to calculate the "nth" day of "nth" quarter, year. (to return a date).

I'd prefer not to use VBA if possible, this will be used by multiple level excel users.

:confused:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try these:

ABCD
1nth Daynth QuarterYearDate
224201402/10/2014

<tbody>
</tbody>

A2, B2, C2 are user inputs

D2: =DATE(C2,LOOKUP(B2,{1,2,3,4},{1,4,7,10}),1)+A2-1

ABC
4DateFirst Day Next Quarter
3rd Thursday
510/01/201401/04/201417/04/2014

<tbody>
</tbody>

A5 is user input

B5 (helper column): =IF(MONTH(A5)>9,DATE(YEAR(A5)+1,LOOKUP(MONTH(A5),{1,2,3,4,5,6,7,8,9,10,11,12},{4,4,4,7,7,7,10,10,10,1,1,1}),1),DATE(YEAR(A5),LOOKUP(MONTH(A5),{1,2,3,4,5,6,7,8,9,10,11,12},{4,4,4,7,7,7,10,10,10,1,1,1}),1))

C5: =B5-DAY(B5)+22-WEEKDAY(B5-DAY(B5)+3)
 
Upvote 0
1st 4 are user inputs 4th is equation =("Day of Week"-WEEKDAY(DATE("Year",1,1))+DATE("Year",1,1)+(("Quarter"-1)*13+"Day of Quarter")*7)
Year
2014
Day Of Week5
Day of Qtr3
Quarter3
Date7/24/2014

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top