Insert today's date in Google Sheets

Question

I use Ctrl+; to insert today's date since my Microsoft Excel times, but how do I do it in Google Sheets app on my Android phone, without a keyboard?

Answer 1

1. Format the cell as a date
2. Select the cell you want to add the date to, and click the calendar icon, the current date is inserted.

Answer 2

I first tried downloading Hacker's Keyboard because it gives you a full PC keyboard on your Android device. In other words, it has a Ctrl key. However, I could not get Ctrl+; to work. I could get other Ctrl+* combinations do their expected functions, but I believe there was an issue with context. The Android keyboard will only popup if you select a text input field. Google Sheets, just like Excel has two input contexts. The first is the selected cell. The second is a cursor in any cell. On Android, I could not get the keyboard to popup while using sheets unless the cursor was in a cell, which made the text input field the formula bar only. The outer "selected cell" context would not call up the keyboard. I believe it would work if you could call up the keyboard in the right context.

I found a workaround that I think is quite tidy. The only issue is that you have to set it up individually for each document on a PC. But once you do set it up, it works very well. The process basically lets you set up a custom formula. In exact terms, we will insert a script on the sheet that will check the value of any cell you edit against a keyword you set. If the keyword matches, it will replace it with the current date and time.

To begin, you will have to open your sheet on a PC and click Tools > Script editor. Enter the following script in the large text field provided. If you've never done this before and don't have to consider any other scripts you've created, then delete the default text that is already there.

function onEdit(e) {
  if (e.range.getValue() == "$new") {
    e.range.setValue(new Date()).setNumberFormat("MM/dd/yyyy HH:mm");
  }
}

If you are unfamiliar with JavaScript, I'll give a quick explanation of each of the parts above. onEdit(e) is a global function, which basically means it's built in and can be called anywhere in the script. It is run every time a cell is edited. The e in the parentheses specifically refers to the active cell. The second link beginning with if gets the value of the recently edited cell e and compares if it is exactly equal to $new. The third line will only run if the second line is true, that is, if the recently edited cell contains exactly the text $new. This line replaces the value with a timestamp. Date() is also a global function which returns the system time. setNumberFormat sets the number format, naturally.

The first obvious thing you can do differently is change the number format to something of your liking. Click here for more information.

You can also change $new to literally anything else. In programming, it is common to use a special character like $ or _ plus a word to denote a custom variable or function. But since it can be anything and this is for my personal use, I opted for ,n. It's weird, certainly, but it's fast and easy to type on an Android keyboard and I can't envision any other instance that I'd start a spreadsheet cell input with a comma.

Going forward, I can see myself using the basics of this script to make other custom functions. Things like pinging a web resource for a certain data point that changes frequently first comes to mind. After that, I'm sure there's all kinds of use cases. If you find yourself "building an Library" of scripts you always want to use on every sheet, well it seems there's a solution to having to add the scripts to every sheet.

Credit for the idea and the script goes to this Webapps Stack Exchange answer..

Answer 3

In the support article Keyboard shortcuts for Google Sheets, the use of keyboard shortcuts is possible with an external keyboard.

From the article:

If you plug an external keyboard into your Android device, you can use the shortcuts below:

It goes on to enumerate the shortcuts. You can enter them with Hacker's Keyboard.

While using Hacker's keyboard, the trick to enter shortcuts is to first enable Use permanent notification in its settings.

Then, in Google sheets, select the cell. In the status bar/notification bar of the phone or tablet, click on Show Hacker's Keyboard. When the keyboard is shown, press Ctrl+;. The date will be entered as if you were using an external keyboard.

Answer 4

First, format the column as date, or date & time, like "13/01 0:00". Then, every time you try to edit, a calendar button will be shown on the bottom right.

Answer 5

Enter the formula =Now() and press enter.