Quarterly inputs

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.
 
S

Sam Wilson

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)
 
L

Loadmaster

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top