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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
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)
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
619
Office Version
  1. 365
Platform
  1. Windows
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

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