Formulas for the first and last day of the quarter?
Solved
Tessel75
-
lahkar7 Posted messages 31 Status Membre -
lahkar7 Posted messages 31 Status Membre -
Hello,
I have several formulas to automatically calculate the first and last days of the month, year, and week based on any given date, and I would like to do the same for the first and last days of the quarter.
(This helps me when I need to select records between two dates and I want my selection to refer to whole periods)
For example
FirstDayOfMonth = DateAdd("d", -Day(d) + 1, d)
LastDayOfMonth = DateAdd("m", 1, d) - Day(d)
with d = any given date
Thank you for your ideas and suggestions
Configuration: Windows 7 / Firefox 30.0
I have several formulas to automatically calculate the first and last days of the month, year, and week based on any given date, and I would like to do the same for the first and last days of the quarter.
(This helps me when I need to select records between two dates and I want my selection to refer to whole periods)
For example
FirstDayOfMonth = DateAdd("d", -Day(d) + 1, d)
LastDayOfMonth = DateAdd("m", 1, d) - Day(d)
with d = any given date
Thank you for your ideas and suggestions
Configuration: Windows 7 / Firefox 30.0
4 réponses
Hello,
if I understood correctly, starting from a date in A1
First day of a quarter: =DATE(YEAR(A1);INT((MONTH(A1)-1)/3)*3+1;1)
Last day of the quarter: =DATE(YEAR(A1);INT((MONTH(A1)-1)/3)*3+4;0)
--
Cheers,
Mike-31
A period of failure is a perfect moment to sow the seeds of knowledge.
if I understood correctly, starting from a date in A1
First day of a quarter: =DATE(YEAR(A1);INT((MONTH(A1)-1)/3)*3+1;1)
Last day of the quarter: =DATE(YEAR(A1);INT((MONTH(A1)-1)/3)*3+4;0)
--
Cheers,
Mike-31
A period of failure is a perfect moment to sow the seeds of knowledge.
Thank you Mike, your formula works well. I'm studying how it works.
At F89__, the question is that I need a floating date, for example starting from today's date. I agree to accept that there is only one first day of the year per year, and 4 first days of the quarter; the problem is that when it comes to automatically setting the boundaries of a period in the middle of a procedure, or in a dialog box, and when working with thousands or tens of thousands of records, then determining this via a formula is essential (or at least I feel that way).
Thanks again.
At F89__, the question is that I need a floating date, for example starting from today's date. I agree to accept that there is only one first day of the year per year, and 4 first days of the quarter; the problem is that when it comes to automatically setting the boundaries of a period in the middle of a procedure, or in a dialog box, and when working with thousands or tens of thousands of records, then determining this via a formula is essential (or at least I feel that way).
Thanks again.
