Warning: strtotime() [function.strtotime]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 35
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 107
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 109
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 111
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 112
Apr
Warning: strtotime() [function.strtotime]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 35
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 107
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 109
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 111
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 112
19
Determining the Fiscal Quarter in Which a Date Falls in Excel 2007
Microsoft Excel, Tips & Tricks Add comments
If you work with budget model or financial worksheets, it’s often handy to know the fiscal quarter in which a date falls. For example, if you’re applying a percentage increase to various budget numbers, you might want to use a different percentage depending on the quarter.
If your fiscal year corresponds with the calendar year, you can calculate the fiscal quarter of any date by using a formula that includes the CEILING() function. First, here’s the CEILÂING() syntax:
CEILING(number, significance)
number The number you want to round.
significance The multiple to which you want number rounded.
CEILING() rounds the value given by number to a multiple of the value given by signifiÂcance. In particular, CEILING() rounds away from 0. For example, CEILING(1.56, 0.1) returns 1.6, and CEILING(-2.33, -0.5) returns -2.5.
To calculate the fiscal quarter in a calendar year for a given Date, divide the month value of Date by 3 and then use CEILING() to round the result up:
=CEILING(MONTH(Date) / 3, 1)
Here are two examples:
=CEILING(MONTH(DATEVALUE(”3/31/2007″)) / 3, 1) – Returns 1 =CEILING(MONTH(DATEVALUE(”4/1/2007″)) / 3, 1) – Returns 2
Most companies use a fiscal year that doesn’t correspond with the calendar year. In this case, you need to calculate the number of months that a given date falls after the start of the fiscal year. The easiest way to do that is to use the DATEDIF() function, which returns the difference between two specified dates based on a specified unit:
DATEDIF(start_date, end_date[, unit])
|
start_date |
The starting date. |
|
|
end date |
The ending date. |
|
|
unit |
The date unit used in the result: |
|
|
|
unit |
What It Returns |
|
|
y |
The number of years between start_date and end_date. |
|
|
m |
The number of months between start_date and end_date. |
|
|
d |
The number of days between start_date and end_date. |
|
|
md |
The difference in the day components between start_date |
|
|
and end_date (that is, the years and months are not |
|
|
|
included in the calculation). |
|
|
ym |
The difference in the month components between |
|
|
start_date and end_date (that is, the years and days are |
|
|
not included in the calculation). |
|
yd |
The number of days between start_date and end_date, |
|
|
with the year components excluded from the calculation. |
For our fiscal quarter calculation, you use the m unit: DATEDIF(FiscalStart, Date, “m”) + 1
Here, FiscalStart is the date on which your fiscal year begins, and Date is the date for which you want to calculate the fiscal quarter. Note that you need to add 1 to the result because DATEDIF() doesn’t include the current month in the calculation. (For example, if Date falls within the first fiscal month, DATEDIF() returns 0.)
You can now divide the DATEDIF() result by 3 and apply the CEILING() function as before:
=CEILING((DATEDIF(FiscalStart, MyDate, “m”) + 1) / 3, 1)
Here are two examples that assume the beginning of the fiscal year is August 1 and that the opening date of the fiscal year resides in cell A1:
=CEILING(DATEDIF(A1, DATEVALUE(”10/31/2007″), “m”) + 1 / 3, 1) – Returns 1
=CEILING(DATEDIF(A1, DATEVALUE(”11/1/2007″), “m”) + 1 / 3, 1) – Returns 2
Technorati Tags: financial worksheet, excel
Popularity: 5% [?]
Recent Comments