如何在Google Sheets中插入不自动更新的昨日日期

Great question! I’ve run into this exact same frustration before—Google Sheets doesn’t ship with a built-in keyboard shortcut for inserting a static yesterday’s date like it does for today (Ctrl+; on Windows, Cmd+; on Mac). But there are a few solid workarounds that give you that non-updating date without relying on auto-refreshing functions like TODAY()-1.

Workaround 1: Custom Function + Paste as Values #

This is my go-to for regular use, since it’s flexible and easy to set up once:

1. Open the script editor by navigating to Extensions > Apps Script
2. Paste this simple function into the editor:

   function YESTERDAY() {
     return new Date(new Date().setDate(new Date().getDate() - 1));
   }
   

3. Save the project (give it a quick name like "Static Date Tools") and close the editor
4. In the cell where you want yesterday’s date, type =YESTERDAY() and hit Enter
5. Now, select the cell, press Ctrl+C (or Cmd+C), right-click, and choose Paste special > Paste values only
6. That’s it! The date is now a static value and won’t change when you reopen the sheet or recalculate data.

Workaround 2: Quick Keyboard Shortcut + Manual Adjustment #

Perfect for one-off uses when you don’t want to set up a script:

1. First, insert today’s static date with Ctrl+; (or Cmd+; on Mac)
2. Double-click the cell to edit the date, or press F2 to enter edit mode
3. Use your arrow keys to jump to the day portion, subtract 1, and press Enter
4. Super fast for occasional needs without extra setup.

Workaround 3: Record a Macro for One-Click Insert #

If you need this often, a custom macro lets you assign a shortcut for instant access:

1. Go to Extensions > Macros > Record macro
2. In the recording window, check "Use relative references" (this ensures it works on any selected cell)
3. Perform these steps while recording:
   • Type =TODAY()-1 in the active cell and press Enter
   • Select the cell again, copy it (Ctrl+C/Cmd+C)
   • Right-click and choose Paste special > Paste values only
4. Click "Save", name the macro something like "Insert Static Yesterday", and optionally assign a keyboard shortcut (e.g., Ctrl+Shift+Y)
5. Now you can run the macro with one click or your custom shortcut to insert a static yesterday’s date instantly.

Important Note #

Remember: Using TODAY()-1 directly will update every time the sheet recalculates (like when you open it or edit other cells). Always convert the formula result to a static value using paste-as-values if you want it to stay fixed.

内容的提问来源于stack exchange，提问作者David O'Brien