Quarterly inputs

  • Thread starter Thread starter Loadmaster
  • Start date Start date
L

Loadmaster

How do i input the last day of the current quarter into cell B1? However, if
the date within cell A1 is within the current quarter then enter the last day
of the next quarter into cell B1. If the date in cell A1 is not at a minumum
of the previous quarter (two quarters ago), then I require a conditional
format to turn cell A1 red.
 
1. Last day of current quarter in cell B1:

=DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1

2. Last day unless A1 is in the current qtr then next qtr last day:

=IF(ROUNDUP(MONTH(A1)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),EDATE(DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1)
 
Thank-you for the formula. I think I already have the conditional formattting
at work.
 
Back
Top