Forum Discussion
Projection for current quarter
Hi Lydia,
Thanks for getting back to me. I managed to solve this in the end by writing the following formula......
First day of Quarter = DATE(YEAR(TODAY()),IF(AND(MONTH(TODAY())<4,MONTH(TODAY())>0),1,IF(AND(MONTH(TODAY())<7,MONTH(TODAY())>3),4,IF(AND(MONTH(TODAY())<10,MONTH(TODAY())>6),7,10))),1)
I appreciate it is a bit clunky but it seems to work!!
Thanks for your help!
Hi, so I really only need to return the date of the start of the quarter to work. i.e. the current date is 21/04/2018 so I want the date of 01/04/2018 to be returned as a date. If the date was the 20/03/2018 then 01/01/2018 would be returned.
I have tried STARTOFQUARTER but on the data set it is returning 29/11/2011 which obviously is not running on today's date and isnt even the date of the start of a quarter.
Is this possible?
Many thanks in advance.
Alex
STARTOFQUARTER function works in my scenario. I would recommend you create a calendar table, create relationship between calendar table and your original table. Then create column as shown in my screenshot to test the function. If you still have issues, please share sample data of your table for us to test.
Regards,
Lydia
Hi Lydia,
Thanks for getting back to me. I managed to solve this in the end by writing the following formula......
First day of Quarter = DATE(YEAR(TODAY()),IF(AND(MONTH(TODAY())<4,MONTH(TODAY())>0),1,IF(AND(MONTH(TODAY())<7,MONTH(TODAY())>3),4,IF(AND(MONTH(TODAY())<10,MONTH(TODAY())>6),7,10))),1)
I appreciate it is a bit clunky but it seems to work!!
Thanks for your help!