TODAY, NOW, ROUND, ROUNDUP, ROUNDDOWN, IFERROR
This lesson rounds out your formula toolkit with the small utility functions you'll reach for in nearly every workbook: TODAY and NOW for live dates, ROUND/ROUNDUP/ROUNDDOWN for controlling decimal precision, and IFERROR for hiding ugly error codes behind clean fallback values. None of these are flashy, but skipping them is exactly what makes spreadsheets look unfinished or unreliable.
Why this skill matters professionally
A workbook full of #DIV/0! and #N/A errors looks broken even when the underlying logic is fine, and a report with a stamped 'as of' date that never updates makes people distrust the numbers. Rounding matters for real money — unrounded currency math can produce penny discrepancies that don't reconcile with a bank statement, which is a real problem in bookkeeping. Every one of these functions is small, but together they're what makes a spreadsheet feel trustworthy and professional instead of like a rough draft.
Learning objectives
- Insert live dates and times.
- Round numbers consistently.
- Suppress ugly error messages with IFERROR.
Background concepts
TODAY vs. NOW
TODAY() returns just the current date; NOW() returns the current date and time. Both are 'volatile' — they recalculate every time the workbook recalculates, not just when you first typed the formula, so a report opened tomorrow will show tomorrow's date automatically.
Why ROUND has three variants
Plain ROUND rounds to the nearest value using standard rounding rules (0.5 and above rounds up). ROUNDUP always rounds away from zero regardless of the decimal, and ROUNDDOWN always rounds toward zero — useful when a business rule requires always rounding in one direction, like never overcharging a customer by rounding up.
Negative numbers of decimal places
ROUND's second argument can be negative to round to the nearest ten, hundred, or thousand: =ROUND(1234,-2) returns 1200. This is a common way to round large financial figures for a summary chart.
What IFERROR actually catches
IFERROR catches any error type — #DIV/0!, #N/A, #VALUE!, #REF!, #NAME? — and returns a fallback of your choosing instead. It doesn't fix the underlying problem; it just controls what displays, so it should be added after you've confirmed the core formula logic is right.
Step-by-step
1. 1. Insert a live date
Type =TODAY() in any cell — it will always show the current date whenever the file is opened or recalculated. Format it as a date (Ctrl+1) if it displays as a serial number.
2. 2. Insert a live date and time
Type =NOW() for both date and time together — useful for timestamping when a report was generated or last refreshed.
3. 3. Calculate elapsed days
=TODAY()-B2 where B2 holds a past date returns the number of days elapsed — great for invoice aging or 'days since last contact' trackers.
4. 4. Round to a fixed number of decimals
=ROUND(A2,2) rounds 12.4567 to 12.46. Use this on any currency calculation before displaying or summing to avoid tiny floating-point discrepancies.
5. 5. Force rounding up regardless of decimal
=ROUNDUP(A2,0) turns 12.01 into 13 — useful when calculating how many boxes are needed to ship a quantity, where any partial box still needs a full box.
6. 6. Force rounding down regardless of decimal
=ROUNDDOWN(A2,2) turns 12.999 into 12.99 — useful for a business rule that says you should never round a customer's favor upward.
7. 7. Round to the nearest ten or hundred
=ROUND(A2,-2) rounds 3482 to 3500, useful for high-level summaries where exact precision isn't needed.
8. 8. Wrap a risky formula in IFERROR
=IFERROR(A2/B2,"N/A") returns "N/A" instead of #DIV/0! if B2 is zero or blank, keeping a report clean when data is incomplete.
9. 9. Combine IFERROR with a VLOOKUP
=IFERROR(VLOOKUP(A2,PriceList,2,FALSE),"Not Found") shows a friendly message any time a lookup value doesn't exist in the reference table, instead of #N/A scattered across the sheet.
Real-world workplace examples
Report timestamp
="Report generated: "&TEXT(NOW(),"m/d/yyyy h:mm AM/PM") stamps exactly when a dashboard was last refreshed, useful for anyone checking whether the numbers are current.
Invoice aging days
=TODAY()-InvoiceDate calculates how many days an invoice has been outstanding, feeding directly into an aging bucket formula.
Currency rounding for reconciliation
=ROUND(Price*Qty*(1-Discount),2) ensures every line item rounds to the penny the same way a bank or accounting system would, avoiding mismatches during reconciliation.
Shipping box calculator
=ROUNDUP(TotalUnits/UnitsPerBox,0) tells a warehouse exactly how many boxes are needed, always rounding up since a partial box still counts as a full box.
Clean lookup dashboard
=IFERROR(XLOOKUP(SKU,Catalog,Price),"Check SKU") keeps a live dashboard free of #N/A clutter when a product code is mistyped or discontinued.
Practical scenarios
Penny drift on a large invoice
A bookkeeper builds a spreadsheet that calculates tax as Price*0.0825 on each line item and sums the column, but the total is off by two cents from what the accounting system calculates, because the accounting system rounds each line to the cent before summing, while the spreadsheet summed unrounded values first. Wrapping each line's tax formula in ROUND(...,2) before summing brings the totals into exact agreement.
Dashboard riddled with #N/A
A sales manager's dashboard pulls commission rates via VLOOKUP for 40 reps, but 3 new hires aren't in the rate table yet, so their rows show #N/A, which breaks a SUM formula further down the sheet (any range containing an error returns an error). Wrapping each VLOOKUP in IFERROR(...,0) lets the SUM calculate correctly for existing reps while flagging the new hires' 0% commission as an obvious placeholder to fix, rather than crashing the whole total.
Common mistakes beginners make
Confusing ROUND with formatting decimal places
Changing the displayed decimal places (Increase/Decrease Decimal buttons) only changes how a number looks, not its stored value, which can cause totals that don't visually add up. Fix: use ROUND() when the actual value needs to change, not just its display.
Using ROUND when ROUNDUP or ROUNDDOWN is needed
Standard ROUND can round in either direction, which breaks business rules like 'always round shipping estimates up.' Fix: choose ROUNDUP or ROUNDDOWN explicitly when the direction matters, not just the nearest value.
Wrapping a formula in IFERROR before checking why it errors
Slapping IFERROR(...,0) onto a broken VLOOKUP hides a real bug (like a typo in the lookup range) instead of fixing it. Fix: diagnose and fix the root cause first, then add IFERROR only to handle legitimately expected gaps in data.
Forgetting TODAY() and NOW() are volatile
A printed or saved copy of a report will show a different date than when it was generated, confusing someone reviewing it later. Fix: for a permanent 'as of' date on an archived report, paste the date as a static value instead of a live formula.
Rounding too early in a multi-step calculation
Rounding an intermediate result before a final multiplication can compound small errors across hundreds of rows. Fix: round only the final displayed or summed value, not every intermediate step, unless a specific business rule requires it.
Best practices
- Round currency calculations to 2 decimals at the point of final total, not at every intermediate step.
- Use ROUNDUP/ROUNDDOWN deliberately whenever a business rule specifies a rounding direction, not just 'nearest.'
- Diagnose the root cause of an error before wrapping a formula in IFERROR.
- Convert TODAY()/NOW() to static values with Paste Special when archiving a report for a fixed point in time.
- Use TEXT() alongside NOW()/TODAY() to control exactly how the date/time displays in reports.
- Choose a consistent fallback value for IFERROR across a workbook (like "N/A" or 0) so results are predictable.
- Test IFERROR fallbacks against a genuinely error-producing input, not just normal data, to confirm the fallback text displays correctly.
Professional tips
- =ROUND(A2,-3) is a fast way to round large financial numbers to the nearest thousand for an executive summary slide.
- IFERROR can wrap an entire nested formula, not just a single function — put it around the whole calculation.
- NETWORKDAYS() combined with TODAY() calculates business days remaining until a deadline, ignoring weekends.
- Use IFNA() instead of IFERROR() when you specifically want to catch #N/A but let other genuine errors (like #REF!) still show, so real mistakes aren't hidden.
- Format TODAY()/NOW() cells explicitly (Ctrl+1) — a fresh formula sometimes displays as a raw serial number like 45678 until formatted as a date.
Practice exercises
Live invoice aging tracker
Build a column of invoice dates and a formula using TODAY() that calculates days outstanding for each row.
Currency rounding fix
Create a list of prices with a tax rate, calculate a total two ways — rounding each line first, and rounding only the final sum — and compare the two results.
Box quantity calculator
Given a total units column and a fixed units-per-box number, use ROUNDUP to calculate boxes needed for each order.
Error-proof a lookup table
Build a small VLOOKUP or XLOOKUP against a short reference table, then deliberately test it with a value that doesn't exist, wrapping the formula in IFERROR to show 'Not Found' instead of an error code.
Review questions
What's the difference between TODAY() and NOW()?
TODAY() returns only the current date; NOW() returns both the current date and time, and both recalculate live whenever the workbook updates.
When would you use ROUNDUP instead of ROUND?
When a business rule requires always rounding in one direction — for example, always rounding a shipping box count up, since a partial box still needs a full box.
What does a negative number of decimal places do in ROUND?
It rounds to the nearest ten, hundred, thousand, etc. — for example ROUND(A2,-2) rounds to the nearest hundred.
What does IFERROR actually fix?
Nothing in the underlying data — it only controls what displays when a formula errors, replacing an error code with a friendly fallback value.
Why might rounding at the wrong step in a calculation cause reconciliation problems?
Rounding too early or too late compared to how another system (like a bank or accounting platform) rounds can create tiny penny-level mismatches that compound across many rows.
Key takeaways
- TODAY() and NOW() are volatile and update automatically every time the sheet recalculates.
- ROUND rounds to the nearest value; ROUNDUP and ROUNDDOWN force a specific direction.
- Negative decimal arguments in ROUND round to tens, hundreds, or thousands.
- IFERROR hides error displays but doesn't fix the underlying cause — diagnose first, wrap second.
- IFNA specifically targets #N/A while letting other error types still surface.
- Round currency at the final total step, not at every intermediate calculation, to avoid drift.
- Convert live date formulas to static values before archiving a report for a fixed point in time.
Frequently asked questions
Will TODAY() change the date if I open the file next week?
Yes — it always reflects whatever the current date is when the file recalculates, unless you convert it to a static value.
Does ROUND change the actual stored value or just the display?
It changes the actual stored numeric value used in further calculations, unlike simply adjusting displayed decimal places.
What's the difference between IFERROR and IFNA?
IFERROR catches every error type; IFNA only catches #N/A, letting other genuine errors like #REF! or #DIV/0! still display so you notice real problems.
Can I use ROUND inside a SUM formula?
Yes, but it only rounds if applied to each value before summing (e.g., inside a helper column or an array formula) — SUM itself doesn't round.
Why does my date formula show a number like 45678 instead of a date?
The cell's format is set to General or Number instead of Date — apply a date format via Ctrl+1 to fix the display.
Can IFERROR wrap a whole nested formula?
Yes, wrap the entire calculation, however complex, inside IFERROR(..., fallback) as one unit.
How do I calculate business days instead of calendar days until a deadline?
Use NETWORKDAYS(TODAY(),DueDate) which automatically excludes weekends (and holidays if you supply a holiday list).
Related lessons
12 min • Beginner
15 min • Easy
20 min • Intermediate
20 min • Advanced