function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SteveA101SteveA101 

Formula for Beg of Qtr

Hi All,

 

I am trying to populate a field based on a date in another field.  For a given date, I would like to backdate the date to the first day of the calendar year Quarter.  For example, if the date in Field A is 5/15/11, I would like to formulate the date in Field B to be 4/1/11.

 

Thanks for your help!

 

Steve

Steve :-/Steve :-/

You probably want to evaluate the MONTH of your Date Field using a CASE function, kinda like this:

 

CASE(MONTH(DateField),
1,1,
2,1,
3,1,
4,4,
5,4,
6,4,
7,7,
8,7,
9,7,
10,10,
11,10,
12,10)

 
SteveA101SteveA101

I wrote a few formulas in excel that worked, but I'm unable to get them to work in SFDC.  One is:

=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1)

 

But I'm getting an error on the FLOOR function...
Jerun JoseJerun Jose

Hi,

 

Going with SteveMo's approach, the formula you are looking for is:

 

DATE(YEAR(A1),
  CASE(MONTH(DateField),
  1,1,
  2,1,
  3,1,
  4,4,
  5,4,
  6,4,
  7,7,
  8,7,
  9,7,
  10,10,
  11,10,
  12,10),
1) 

 

The floor operator in SFDC works with just numbers. so you should be using it as floor(number/3)

The Excel formula you gave would work only for the first quarter. For the second quarter, it will give the month as 2.

 

Regards,

 

Jerun Jose