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:
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
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
539
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,385
Messages
5,528,396
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top