15.1 Currency Formatting in Excel

Currency formatting does not change the underlying numeric value — it controls only how the number displays. Excel stores 1500.5 regardless of whether you see it as R 1,500.50, $1,500.50, or just 1500.5. Calculations always use the stored value.

Applying the Rand (R) Currency Format

Custom Rand Format Codes

Currency vs Accounting Format

Multi-Currency Worksheets

When working with multiple currencies (Rand, USD, EUR, GBP) in the same workbook:

• Store all amounts in their original currency — do not pre-convert
• Apply the appropriate currency format to each column or cell
• Use a separate exchange rate table for conversions: =USD_Amount * ExchangeRate_USD_ZAR
• Flag the currency in a separate column (text: "ZAR", "USD", "EUR") to avoid ambiguity

15.2 How Excel Stores Dates — The Date Serial Number

Understanding this is the key to all date calculations. Excel does not store dates as text like "15 January 2025". It stores every date as a serial number — an integer representing the number of days since a fixed starting point.

The Date Serial System

Why This Matters

• Because dates are numbers, you can subtract two dates to find the number of days between them: =B2-A2
• You can add days to a date: =A2+30 gives you the date 30 days after A2
• You can use dates in SUM, AVERAGE, MAX, MIN: =MAX(A2:A100) finds the latest date
• If a date displays as a number (e.g., 45658), the cell format is set to General or Number instead of a date format — apply a date format to fix the display

Checking a Date's Serial Number

• Select a cell with a date → press Ctrl+Shift+~ (General format) — the serial number appears
• Or: =DATEVALUE("15/01/2025") returns the serial number for that date text
• Press Ctrl+Z to restore the date format

15.3 Date Entry & Formatting

Entering Dates Correctly

Date Format Codes

Apply via Ctrl+1 → Number → Custom:

15.4 Essential Date Functions

TODAY() and NOW()

DATE(), YEAR(), MONTH(), DAY() — Building and Extracting Dates

TEXT Function for Date Formatting

Convert a date to a formatted text string for display in concatenated text:

15.5 Date Difference Calculations

Calculating the difference between two dates is one of the most common date tasks in Excel — for age calculations, tenure, project duration, days overdue, and lease terms.

Simple Date Subtraction

DATEDIF — The Date Difference Function

DATEDIF (Date Difference) calculates the difference between two dates in complete years, months, or days. It is a hidden function — it does not appear in IntelliSense but it works in all Excel versions.

Age Calculation — Full Example

NETWORKDAYS — Working Days Between Two Dates

• Returns the number of working days (Monday–Friday) between two dates, excluding weekends
• Optional holidays argument: a range listing public holidays to exclude (e.g., your SA public holidays list)

NETWORKDAYS.INTL — Custom Weekends

Use NETWORKDAYS.INTL when your organisation's non-working days are not Saturday+Sunday (e.g., some sectors work Saturdays; some regions have Friday+Saturday as weekend).

WORKDAY — Find a Date N Working Days Later

Use WORKDAY for: delivery deadlines (ship date + 5 working days = expected delivery), payment due dates, SLA response deadlines.

15.6 SA Public Holidays — Setting Up a Holiday Table

For NETWORKDAYS and WORKDAY to exclude SA public holidays, create a holiday table on a dedicated sheet:

Creating the SA Public Holiday Table

1. Add a sheet named "PublicHolidays"
2. Column A: holiday dates (as proper Excel date values)
3. Column B: holiday names (for reference)
4. Name the date range: select A2:A20 → Name Box → type SAHolidays → Enter

15.7 How Excel Stores Time — Fractional Day Numbers

Time in Excel is stored as a decimal fraction of a day. Midnight = 0, noon = 0.5, end of day = 0.999… This means a cell can hold a date-and-time combined as a single number: the integer part = the date, the decimal part = the time.

Entering Times

Time Format Codes

15.8 Time Calculations

Calculating Hours Worked

Converting Time to Hours for Payroll

TIME() Function — Creating Time Values

HOUR(), MINUTE(), SECOND() — Extracting Time Components

Handling Times Past Midnight

When end time is after midnight (night shift: start 22:00, end 06:00 next day), simple subtraction gives a negative result. Fix:

15.9 SA Payroll & Finance Date Scenarios

Employee Age Verification (FICA/Hiring)

Leave and Payroll Calculations

Invoice and Payment Terms

Financial Year Calculations (SA: 1 March – 28/29 February)

15.10 Quick Self-Check

Q1: A cell containing the date 15/01/2025 suddenly shows "45658" instead. What happened and how do you fix it?

✓ The cell format was changed to General or Number, so the underlying serial number is now visible instead of the formatted date. Excel stores every date as a serial number (days since 1 January 1900 — 45658 = 15 January 2025). Fix: select the cell → Home → Number Format dropdown → Short Date (or Long Date) — or press Ctrl+1 → Number → Date → choose the desired date format. The date 15/01/2025 reappears. The serial number has not changed; only the display format was lost.

Q2: An employee's date of birth is in cell A2 (15/08/1990). Write a formula that displays their age in years and months in a readable format, e.g., "34 years 5 months".

✓ =DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")&" months". The first DATEDIF with "Y" returns the complete years (34). The second DATEDIF with "YM" returns the additional months beyond the last complete year (5). The & operator joins the text into a readable sentence. Note that the end_date must be after the start_date in DATEDIF or it returns a #NUM! error — use TODAY() which is always after any birth date.

Q3: You are calculating the hours worked for a timesheet. Cell A2 = 08:00 (start) and B2 = 17:30 (end). After entering =B2-A2 and summing all 5 days, the weekly total shows 19:15 instead of 43:15. What is the problem and how do you fix it?

✓ The total cell is formatted with hh:mm which resets to zero at 24 hours. 43:15 rolls over twice past 24, displaying 43-24-24=−5, then 24+(−5)=19:15. Fix: select the total cell → Ctrl+1 → Number → Custom → type [h]:mm → OK. The square brackets around h tell Excel to show the true elapsed hours without resetting at 24, so 43:15 displays correctly. This is essential for any timesheet where weekly hours can exceed 24.

Q4: An invoice was issued on 15 January 2025 (in A2) with 30-day payment terms. Write formulas to: (a) calculate the due date, (b) show whether the invoice is overdue or current, (c) count the days overdue if it is past due.

✓ (a) Due date: =A2+30. Simple date addition — Excel adds 30 days to the date serial number, giving 14 February 2025. (b) Status: =IF(TODAY()>A2+30,"OVERDUE","Current"). Compares today's date to the due date. (c) Days overdue: =MAX(0,TODAY()-(A2+30)). If today is before the due date, TODAY()-due_date is negative, and MAX(0,...) returns 0 (not yet due). If today is after the due date, it returns the positive number of days past due.

Q5: You need to calculate the number of working days between two project dates, excluding weekends AND South African public holidays (stored in a named range "SAHolidays"). The start date is in A2, the end date in B2. Write the formula.

✓ =NETWORKDAYS(A2,B2,SAHolidays). NETWORKDAYS counts working days (Monday through Friday) between two dates inclusively. The third argument (SAHolidays) specifies a list of additional non-working days to exclude. The formula automatically skips all weekends and all dates listed in the SAHolidays range. Note: NETWORKDAYS counts both the start and end dates if they fall on working days (it is inclusive at both ends), so =NETWORKDAYS(A2,B2)-1 if you want to exclude the start date.

Q6: What is the difference between the Currency and Accounting number formats in Excel? Which is preferred for financial statements and why?

✓ Currency format: places the symbol immediately adjacent to the number (R1,500.50), shows zero as R0.00, and negatives in red or with a minus sign. Accounting format: aligns the currency symbol to the far left of the cell with the number right-aligned (R  1,500.50), shows zero as a dash (—), and negatives in brackets. For financial statements, the Accounting format is preferred because: (1) decimal points always align vertically in a column making figures easier to compare; (2) the symbol alignment is consistent and professional; (3) zeros as dashes are the accounting convention distinguishing a zero balance from a missing entry; (4) negatives in brackets is the standard accounting notation rather than a minus sign which can be confused with a hyphen.

✓ Module 15 Complete — You Have Learned:

• Currency formatting — Accounting button ($) dropdown for Rand; Custom format codes for R with decimals, whole Rand, negatives in brackets/red, millions/thousands abbreviations; Currency vs Accounting format (5-row comparison — symbol position, zeros, negatives, alignment); multi-currency workbook approach
• Date serial numbers — Excel stores dates as integers (days since 1 Jan 1900); serial number examples; why this enables date arithmetic; how to view the serial (Ctrl+Shift+~); fixing dates that display as numbers
• Date entry — DD/MM/YYYY as SA standard; all accepted formats; Ctrl+; for static today; SA date entry warning (Windows regional settings DD/MM vs MM/DD)
• Date format codes — dd/mm/yyyy, dd mmm yyyy, dd mmmm yyyy, dddd comma date, mmm-yy, mmmm yyyy, yyyy-mm-dd (ISO)
• Date functions — TODAY() and NOW() (auto-update); DATE(y,m,d) to construct; YEAR/MONTH/DAY to extract; WEEKDAY with return_type 2 (1=Mon, 7=Sun); EOMONTH for month-end dates; EDATE for same-day N months later; TEXT for date-in-string formatting
• Date difference — simple subtraction (format result as Number not Date); DATEDIF with all 6 units ("Y","M","D","YM","MD","YD"); full age-in-years-and-months formula example
• NETWORKDAYS — working days between dates excluding weekends; optional holidays argument; NETWORKDAYS.INTL for custom weekend definitions; WORKDAY to find date N working days from a start
• SA Public Holidays table — 12 SA public holidays 2025; creating a SAHolidays named range; using it in NETWORKDAYS and WORKDAY
• Time serial fractions — midnight=0, noon=0.5; time entry formats; time format codes (hh:mm, h:mm AM/PM, hh:mm:ss, [h]:mm for elapsed hours >24)
• Time calculations — hours worked (B2-A2); converting to decimal hours (*24); total weekly hours with [h]:mm format; TIME(h,m,s) to create; HOUR/MINUTE/SECOND to extract; overnight shift formula (MOD or IF)
• SA payroll/finance formulas — age verification, retirement eligibility, leave days (NETWORKDAYS), annual leave entitlement, next payroll date (WORKDAY+EOMONTH), invoice due date, days overdue (MAX(0,...)), financial year start/end (SA: 1 March–28/29 Feb)