The first thing most people do when they need a 'current date and time' in Excel is type =NOW() into a cell and call it done. That’s usually the wrong move — especially if you’re generating invoices, audit logs, or compliance-ready reports. Because NOW() isn’t static. It refreshes whenever Excel recalculates — which happens on open, save, edit, or even scrolling in some cases. So that ‘signed on’ timestamp you put in cell D2? It might show 2024-03-15 09:17 AM when you print — but by noon, it’s quietly become 2024-03-15 12:03 PM. And nobody told you.
The Setup
You’re auditing a small procurement log for Alta Dynamics, a hardware reseller in Shenzhen. Their team uses Excel (not a database) to track vendor submissions — and leadership wants a clean, auditable record of when each quote was received and reviewed. No timestamps should shift after entry. The raw data lives in Sheet1, columns A through E:
| A: Vendor | B: Quote ID | C: Amount | D: Received Date | E: Review Status |
|---|---|---|---|---|
| Shenzhen OptoTech Ltd | QT-2024-0881 | $14,620 | Pending | |
| Ningbo Precision Components | QT-2024-0882 | $8,950 | Pending | |
| Guangzhou SmartFab Co. | QT-2024-0883 | $22,300 | Pending | |
| Suzhou NanoTools Inc | QT-2024-0884 | $5,175 | Pending | |
| Chengdu ElectroSystems | QT-2024-0885 | $17,890 | Pending | |
| Xiamen DataLink Group | QT-2024-0886 | $11,430 | Pending | |
| Foshan CircuitWorks | QT-2024-0887 | $6,210 | Pending | |
| Hangzhou QuantumLogic | QT-2024-0888 | $31,500 | Pending |
The Challenge
You’re asked to fill column D (Received Date) with the exact date and time each quote arrived — and then lock it down. Not ‘whenever I opened this file’, not ‘when I hit F9’. The finance team needs proof: Quote QT-2024-0882 was received at 2024-03-15 14:22:07. But NOW() gives you a moving target. Paste it into D2, and two hours later? It’s updated — even if no one touched that cell. Worse: if someone opens the file on a different machine with a different system clock, or hits Ctrl+Alt+F9 (full recalc), your timestamps drift. You can’t audit what you can’t trust.
Walking Through It
Here’s what actually works — and why most tutorials skip step 2:
Step 1: Insert NOW() — but only as a temporary helper
Type =NOW() into cell F2. That’s right — not D2. Keep it separate. Press Enter. You’ll see something like 2024-03-15 14:22:07. Good. Now copy that cell (Ctrl+C).
Step 2: Paste Values — the critical step everyone forgets
Select cell D2. Then press Alt+H, V, V. That’s the keyboard shortcut for Paste Special → Values. You just converted the volatile formula into a fixed datetime value. Check the formula bar: it now shows 3/15/2024 14:22:07 — no equals sign, no parentheses. It’s frozen.
Now repeat for rows 3–9. But don’t retype =NOW() each time. Instead: copy F2 again, select D3:D9, then hit Alt+H, V, V. Done.
Step 3: Format cleanly
Select D2:D9, right-click → Format Cells → Category: Date or Time, or better: Custom → enter yyyy-mm-dd hh:mm:ss. That gives you ISO-style clarity — no regional ambiguity.
Before (volatile):
| D: Received Date | E: Review Status |
|---|---|
=NOW() |
Pending |
=NOW() |
Pending |
After (static):
| D: Received Date | E: Review Status |
|---|---|
| 2024-03-15 14:22:07 | Pending |
| 2024-03-15 14:22:07 | Pending |
The Result
Here’s what your final Sheet1 looks like — with fully locked, audit-ready timestamps:
| A: Vendor | B: Quote ID | C: Amount | D: Received Date | E: Review Status |
|---|---|---|---|---|
| Shenzhen OptoTech Ltd | QT-2024-0881 | $14,620 | 2024-03-15 14:22:07 | Pending |
| Ningbo Precision Components | QT-2024-0882 | $8,950 | 2024-03-15 14:22:11 | Pending |
| Guangzhou SmartFab Co. | QT-2024-0883 | $22,300 | 2024-03-15 14:22:15 | Pending |
| Suzhou NanoTools Inc | QT-2024-0884 | $5,175 | 2024-03-15 14:22:19 | Pending |
| Chengdu ElectroSystems | QT-2024-0885 | $17,890 | 2024-03-15 14:22:23 | Pending |
| Xiamen DataLink Group | QT-2024-0886 | $11,430 | 2024-03-15 14:22:27 | Pending |
| Foshan CircuitWorks | QT-2024-0887 | $6,210 | 2024-03-15 14:22:31 | Pending |
| Hangzhou QuantumLogic | QT-2024-0888 | $31,500 | 2024-03-15 14:22:35 | Pending |
Note the millisecond-level consistency in seconds — because you pasted values *immediately* after entering NOW(), not minutes later. That’s how you get precision.
What Could Go Wrong
Three real mistakes we’ve seen — all from skipping that Paste Values step or misapplying it:
Mistake #1: Copy-pasting =NOW() directly into the target column
You type =NOW() in D2, then drag the fill handle down to D9. Every cell now contains =NOW(). All eight cells update simultaneously on every recalc. Your ‘received times’ are identical — and meaningless. The finance team spots it instantly: “These all came in at *exactly* the same second?”
Mistake #2: Using Paste Values too late
You wait until end-of-day to paste values — but your laptop went to sleep at 3:45 PM. When you wake it up at 5:10 PM and hit Alt+H, V, V, you’ve just stamped all quotes with 5:10 PM. Even though the first one arrived at 9:03 AM. The log is now unusable for SLA tracking.
Mistake #3: Forgetting to format as datetime (and seeing serial numbers)
You paste values correctly — but leave D2:D9 unformatted. Excel displays 45365.5987 instead of 2024-03-15 14:22:07. Colleagues think it’s an error. They try editing it — and break the value. Always apply formatting *after* pasting values, not before.
Next step: Open your procurement log right now. Pick one row where column D is blank. Type =NOW() in a spare cell (say, Z1), copy it, select your target cell, then hit Alt+H, V, V. Done. Repeat for each row — but only *once per entry*, not once per day.
