Quarterly inputs
 

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.

Quarterly inputs
 

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(TODA Y())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1)



"Loadmaster" wrote:

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.

Quarterly inputs
 

Thank-you for the formula. I think I already have the conditional formattting
at work.

"Sam Wilson" wrote:

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(TODA Y())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1)



"Loadmaster" wrote:

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.