Calculate first and last day of a certain quarter

TruffleOil

New Member
Joined
Sep 9, 2022
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone

I'm trying to calculate the first and last day of a specific quarter (Q3 2022). In the picture attached:
-I have a fixed delivery date (16 Oct 2022)
-I managed to calculate delivery quarter (Q4 2022): ="Q" &INT((MONTH(B3)+2)/3) & "-" & YEAR(B3)
-I also need the quarter prior to delivery (Q3 2022): ="Q" &INT((MONTH(EDATE(B3,-1))+2)/3) & "-" & YEAR(EDATE(B3,-1))

But in the end what I really need is to be able to show the first and last day of the quarter prior to delivery, so for Q3 2022, it would be 1 July 2022 and 30 Sept 2022.
Can anyone please help with a formula for that? I don't want to subtract 90 days from the first day of delivery quarter because it doesn't always show accurate dates (in this case if I do 1st Oct-90 days = 3 July).
Thank you!
 

Attachments

  • excel question 3.PNG
    excel question 3.PNG
    16.4 KB · Views: 4

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,379
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
A lookup and a math way (How to calculate quarter start date or end date based on a given date in Excel?).
Book1
BCD
2Example
3Delivery Date16/10/2022
4Delivery QuarterQ4-2022
5Quarter Prior to DeliveryQ3-2022
6What I need:1/07/202230/09/2022
71/07/202230/09/2022
81/07/202230/09/2022
Sheet1
Cell Formulas
RangeFormula
C7:C8C7=EOMONTH(D7,-3)+1
D7D7=SWITCH(CEILING(MONTH(C3)/3,1),1,DATE(YEAR(C3)-1,12,31),2,DATE(YEAR(C3),3,31),3,DATE(YEAR(C3),6,30),4,DATE(YEAR(C3),9,30))
D8D8=EOMONTH(DATE(YEAR(C3),((INT((MONTH(C3)-1)/3)+1)*3)+1,1)-1,-3)
 
Upvote 0
Solution

TruffleOil

New Member
Joined
Sep 9, 2022
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
A lookup and a math way (How to calculate quarter start date or end date based on a given date in Excel?).
Book1
BCD
2Example
3Delivery Date16/10/2022
4Delivery QuarterQ4-2022
5Quarter Prior to DeliveryQ3-2022
6What I need:1/07/202230/09/2022
71/07/202230/09/2022
81/07/202230/09/2022
Sheet1
Cell Formulas
RangeFormula
C7:C8C7=EOMONTH(D7,-3)+1
D7D7=SWITCH(CEILING(MONTH(C3)/3,1),1,DATE(YEAR(C3)-1,12,31),2,DATE(YEAR(C3),3,31),3,DATE(YEAR(C3),6,30),4,DATE(YEAR(C3),9,30))
D8D8=EOMONTH(DATE(YEAR(C3),((INT((MONTH(C3)-1)/3)+1)*3)+1,1)-1,-3)
bless, it worked!! thank you!
 
Upvote 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,673
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
another alternative
I used XLookup; you can try Vlookup.

T202211a.xlsm
ABCDEFGH
1info
2Example1-Jan-2231-Mar-22Q1 2022
3Delivery Date16-Oct-221-Apr-2230-Jun-22Q2 2022
4Delivery QuarterQ4 20221-Jul-2230-Sep-22Q3 2022
51-Oct-2231-Dec-22Q4 2022
6Previous quarter1-Jul-2230-Sep-221-Jan-2331-Mar-23Q1 2023
7or1-Jul-2230-Sep-221-Apr-2330-Jun-23Q2 2023
81-Jul-2330-Sep-23Q3 2023
91-Oct-2331-Dec-23Q4 2023
4c
Cell Formulas
RangeFormula
C4C4=XLOOKUP(C3,F2:F9,H2:H9,,-1)
C6C6=XLOOKUP(EDATE(C3,-3),F2:F9,F2:F9,,-1)
D6D6=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)
C7C7=XLOOKUP(EDATE(C3,-3),{44562;44652;44743;44835;44927;45017;45108;45200},{44562;44652;44743;44835;44927;45017;45108;45200},,-1)
D7D7=XLOOKUP(EDATE(C3,-3),{44562;44652;44743;44835;44927;45017;45108;45200},{44651;44742;44834;44926;45016;45107;45199;45291},,-1)
 
Upvote 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,673
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202211a.xlsm
CD
101-Jul-2230-Sep-22
4c
Cell Formulas
RangeFormula
C10C10=VLOOKUP(EDATE(C3,-3),F2:H9,1)
D10D10=XLOOKUP(EDATE(C3,-3),F2:F9,G2:G9,,-1)
 
Upvote 0

Forum statistics

Threads
1,187,202
Messages
5,962,183
Members
438,592
Latest member
vincethesun

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