Calc 1st Day of quarter

  • Thread starter Thread starter Martin Just
  • Start date Start date
M

Martin Just

Hi all. I need a formula that will return the first day of a specified date's quarter. Quarters start on Jan 1, Apr 1, Jul 1, Oct 1 in every year. If cell A1 = Nov 30, 2004, I'd like cell B1 to return Oct 1, 2004.

Thanks!
 
Hi all. I need a formula that will return the first day of a specified date's quarter. Quarters start on Jan 1, Apr 1, Jul 1, Oct 1 in every year. If cell A1 = Nov 30, 2004, I'd like cell B1 to return Oct 1, 2004.
Click to expand...


=DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+1,1)


--ron
 
Martin said:
Hi all. I need a formula that will return the first day of a specified
date's quarter. Quarters start on Jan 1, Apr 1, Jul 1, Oct 1 in every
year. If cell A1 = Nov 30, 2004, I'd like cell B1 to return Oct 1, 2004.

Thanks!
Click to expand...
The following should do the trick:
=IF(MONTH(A1)<=3,CONCATENATE("01/01/",YEAR(A1)),IF(MONTH(A1)<=6,CONCATENATE("01/04/",YEAR(A1)),IF(MONTH(A1)<=9,CONCATENATE("01/07",YEAR(A1)),CONCATENATE("01/10/",YEAR(A1)))))

and this also takes into account the year so that if cell A1= 23/05/07
then B1 returns 01/04/07 (note that this is in the British format of
dd/mm/yyyy so may need amending for your personal prefernce)
 
Geoffrey Swindale said:
The following should do the trick:
=IF(MONTH(A1)<=3,CONCATENATE("01/01/",YEAR(A1)),IF(MONTH(A1)<=6,CONCATENATE(
"01/04/",YEAR(A1)),IF(MONTH(A1)<=9,CONCATENATE("01/07",YEAR(A1)),CONCATENATE
("01/10/",YEAR(A1)))))

and this also takes into account the year so that if cell A1= 23/05/07
then B1 returns 01/04/07 (note that this is in the British format of
dd/mm/yyyy so may need amending for your personal prefernce)
Click to expand...


Or
=DATE(YEAR(A1),MONTH(A1)-CHOOSE(MONTH(A1),0,1,2,0,1,2,0,1,2,0,1,2),0)+1

Regards

Sandy
 
Back
Top