Module 7
Numbers, dates and conditional columns
Rounding, date parts, durations and conditional logic.
You'll be able to
- Round correctly
- Derive date parts and periods
- Build conditional columns
Lessons
- 1.Conditional columns with if … then … elseWrite conditional logic that is correct at the boundaries.
- 2.Rounding numbersRound results to a fixed number of decimals and know which rounding mode applies.
- 3.Date parts and durationsDerive reporting periods and elapsed days from real date values.
- 4.Month starts, month ends and period keysTurn dates into reporting periods and calculate due dates from period boundaries.
Take the numbers, dates and conditional columns quiz (8 questions)
Exercises
- beginner~7 minPro
Label orders as Small, Medium or Bulk (kite shop)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.
- beginner~7 minPro
Label orders as Small, Medium or Bulk (bakery)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.
- beginner~7 minPro
Label orders as Small, Medium or Bulk (bike rental)
Add a Tier column from Qty: 1–2 Small, 3–5 Medium, 6 or more Bulk.
- intermediate~7 minPro
Add 20% VAT and round to cents (kite shop)
Calculate a gross price including 20% VAT, rounded to two decimals.
- intermediate~7 minPro
Add 20% VAT and round to cents (bakery)
Calculate a gross price including 20% VAT, rounded to two decimals.
- intermediate~7 minPro
Add 20% VAT and round to cents (bike rental)
Calculate a gross price including 20% VAT, rounded to two decimals.
- beginner~7 minPro
Add year, month and quarter columns (kite shop)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- beginner~7 minPro
Add year, month and quarter columns (bakery)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- beginner~7 minPro
Add year, month and quarter columns (bike rental)
Convert OrderDate to a date, then add Year, Month (name) and Quarter (number).
- intermediate~8 minPro
Calculate days to close each ticket (telecom)
Convert Opened and Closed to dates and compute whole days between them; open tickets stay null.
- intermediate~8 minPro
Calculate days to close each ticket (saas)
Convert Opened and Closed to dates and compute whole days between them; open tickets stay null.
- intermediate~8 minPro
Calculate reimbursement dates (consultancy)
Claims are paid 10 days after the end of the month they were made in. Add Pay Date.
- intermediate~8 minPro
Calculate reimbursement dates (school)
Claims are paid 10 days after the end of the month they were made in. Add Pay Date.
- advanced~10 minPro
Show each region's share of total revenue (kite shop)
Total revenue per region, then each region's percentage of the grand total.
- advanced~10 minPro
Show each region's share of total revenue (bakery)
Total revenue per region, then each region's percentage of the grand total.
- advanced~10 minPro
Show each region's share of total revenue (bike rental)
Total revenue per region, then each region's percentage of the grand total.
- intermediate~8 minPro
Round halves two ways: to even and away from zero (ledger)
Finance rounds half-amounts away from zero, but Power Query's default rounds them to the nearest even number. Show both.
- intermediate~8 minPro
Round halves two ways: to even and away from zero (fuel)
Finance rounds half-amounts away from zero, but Power Query's default rounds them to the nearest even number. Show both.
- advanced~10 minPro
Calculate age in completed years on a report date (rowing club)
Work out each member's age on 30 June 2026, counting only birthdays that have already happened.
- advanced~10 minPro
Calculate age in completed years on a report date (library cards)
Work out each member's age on 30 June 2026, counting only birthdays that have already happened.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (kite shop)
Label each order with the Monday of its week and count orders per week.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (bakery)
Label each order with the Monday of its week and count orders per week.
- intermediate~8 minPro
Count orders per week, with weeks starting on Monday (bike rental)
Label each order with the Monday of its week and count orders per week.
- beginner~7 minPro
Turn minutes into hours and minutes (helpline)
Show each call's duration as whole hours, leftover minutes and a label like 2h 05m.
- beginner~7 minPro
Turn minutes into hours and minutes (consulting)
Show each call's duration as whole hours, leftover minutes and a label like 2h 05m.
- intermediate~7 minPro
Label orders with an April–March fiscal year (kite shop)
The company's fiscal year runs from 1 April to 31 March and is named after the year it ends in.
- intermediate~7 minPro
Label orders with an April–March fiscal year (bakery)
The company's fiscal year runs from 1 April to 31 March and is named after the year it ends in.
- intermediate~7 minPro
Label orders with an April–March fiscal year (bike rental)
The company's fiscal year runs from 1 April to 31 March and is named after the year it ends in.