Practice 11: Custom Number & Date Formatting

Key Topics & Core Concepts

Custom Format Structure: Constructing 4-part format masks: Positive; Negative; Zero; Text.
Currency & Accounting Masks: Creating custom currency alignments, zero dashes ("-"), and parenthetical negatives (($#,##0)).
Date Serial Codes: Displaying custom weekday and date masks using combinations of ddd, mmmm, and yyyy.
Text Append Codes: Appending literal strings to numeric values using escaped quotation syntax (e.g., 0" Units" or $#,##0" USD").

Assignment Overview & Deliverables

In this practice exercise, you will format an executive rental summary by creating custom number formats, specialized date displays, and aligned currency symbols.

Starter Workbook: Download PerfectRentals-04.xlsx
Completed File Name: [your initials] Practice11_CustomFormats.xlsx
Active Worksheets: Summary, Rates, and Transactions

Step-by-Step Instructions

Step 1: Open Starter & Prepare Custom Number Ranges

Download and open PerfectRentals-04.xlsx in Excel.
Click Enable Editing if opened in Protected View.
Save the workbook as [your initials] Practice11_CustomFormats.xlsx.
Click the Summary worksheet tab.

Step 2: Construct 4-Part Currency Format Masks

Select the Net Revenue cells in column E (E5:E20).
Press Ctrl + 1 (or Cmd + 1 on Mac) to open the Format Cells dialog.
On the Number tab, click Custom at the bottom of the Category list.
In the Type box, enter the standard 4-section mask: $#,##0;($#,##0);"-";@
Click OK. Verify that positive numbers display with currency signs, negatives appear in parentheses, and zeros display as centered dashes.

Step 3: Append Text Strings to Numeric Values

Click the Rates worksheet tab.
Select the Total Nights column (C5:C18).
Open Format Cells > Custom.
In the Type field, type: 0" Nights" and click OK.
Verify that cell values remain real numbers for arithmetic calculations while visually displaying the "Nights" text suffix.

Step 4: Format Long Dates with Custom Weekdays

Click the Transactions worksheet tab.
Select the Check-In Date column (B5:B30).
Open Format Cells > Custom.
Enter the following custom date pattern: dddd, mmmm dd, yyyy
Click OK. Verify that serial dates expand to full weekdays and month names (e.g., "Friday, October 16, 2026").

Step 5: Alignment Polish & Save Workbook

Select custom formatted numeric columns and confirm right-alignment.
Double-click column dividers across all three sheets to auto-fit widths and ensure no ### overflow errors exist.
Save your workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area	Verification Target
Custom Number Masks	4-part format structure (`$#,##0;($#,##0);"-";@`) correctly implemented.
String Appending	Text units added to numeric cells via format masks without converting values to strings.
Serial Date Formatting	Full weekday and spelled-out month masks configured using `dddd, mmmm dd, yyyy`.
Presentation Quality	Clean column widths with zero truncation or alignment defects.