Dates, Times, and Custom Formats

Expense reports and budget deadlines need consistent dates. Wrong format makes September look like a division problem.

On the Expenses sheet, James Okonkwo typed 9/3/2025. Some staff read it as 9 March; others as 3 September. A clear dd-mmm-yyyy format removed the confusion for the finance committee.

Open NorthgateBudget.xlsx. You will format dates on Expenses and practise custom codes on the Budgets sheet.

Excel stores dates as serial numbers — days since 1 January 1900 (Windows default). A date serial number is the stored value; the display is controlled by format.

1 displays as 1 Jan 1900. 45901 might display as 3 Sep 2025.

The display is format. The stored value is a number — so you can subtract dates to get days between them.

Switch to the Expenses sheet. In A2, type 3 Sep 2025 and press Enter. Enter the remaining dates from the sample log (or use Ctrl+; for today's date in a spare cell).

Select column A (dates). Home → Number → Short Date or Long Date.

Expected result: Dates look consistent. Click A2 — formula bar shows a serial number or a date, not text.

Example: Row 2 of the expense log shows 3 Sep 2025 for Year 9 novels. Click the Date cell. The formula bar may show 45903 or 03/09/2025 depending on regional settings — same date, different mask.

12:00 is stored as 0.5 (half a day). 18:00 is 0.75.

Combine date and time: =A2+TIME(14,30,0) adds 2:30 pm to a date cell.

For the budget course, dates matter more than times — but knowing times are numeric explains odd decimals if you ever open a cell formatted as General.

A custom number format lets you control exactly how a value displays. Open Home → Number → More Number Formats → Custom.

Type codes in the Type box. Excel shows a Sample preview before you click OK.

On Budgets, click B1 (Q1 header). Open Format Cells → Number → Custom. If B1 holds the number 1, try format "Quarter "0 to display Quarter 1. Alternatively keep Q1 (£) as plain text in the header — headers are usually text, not serial dates.

On A2, use custom "Dept: "@ to show Dept: English — @ is the text placeholder in custom formats.

If you type September 2025 in a cell formatted as Text, DATEDIF and date arithmetic fail. Enter a real date (3 Sep 2025) or use DATE(2025,9,3). Then apply a date format.