Microsoft Office Tutorials and References
In Depth Information
At this point, you can type the closing parenthesis and then press Enter. The formula is
automatically copied for all the rows of the table in the same column, with the result that you can
see in Figure 7-7 (after you adjust the format of the Year column to General in case it was a
different format that you copied from the Date column).
FIguRE 7-7 The Year column calculated for all the rows.
With this technique, you can define all the columns that are useful for navigating the data
that aggregate date in several ways.
Figure 7-8 shows the final result of a complete Dates table with fiscal year starting on July 1.
You can find this table in the CH07-01-Calendar.xlsx workbook included on the companion
DVD. Table 7-1 contains the formula definitions for all of the columns.
FIguRE 7-8 A complete Dates table with fiscal year starting on July 1.
TABLE 7-1 Formula definitions for the Dates table in Excel.
Column Formula
Year =YEAR([@Date])
MonthNumber =MONTH([@Date])
Month
=TEXT([@Date],"MM - mmmm")
Day
=DAY([@Date])
WeekDay
=TEXT([@Date],"dddd")
Quarter
="Q" & ROUNDUP(MONTH([@Date]) /3,0)
FiscalYear
="FY-" & [@Year]+IF([@MonthNumber]<7,0,1)
FiscalQuarter
="FQ" &MOD(CEILING(22+[@MonthNumber]-6-1,3)/3,4)+1
Search JabSto ::




Custom Search