Google Spreadsheets has always been one of my favorite tools. I was an early adopter (I quickly switched to the tool shortly after Google discontinued Google Notebook) and since then I’ve been happy to see all the hard work and hundreds of improvements the Google Docs team have been introducing.
Despite the fact that there are a few great alternatives, I have never really thought about switching. Google Docs (and Google Spreadsheets) have always been the most reliable tool for me and, like with any really great tools, there are plenty of (partially) hidden features inside Google Spreadsheet you may be amazed to discover.
Today’s post is about two really cool Google Spreadsheet functions that allow you to always display the current date and time in your spreadsheet. This will become particularly useful if you use Google Spreadsheets to monitor your tasks and deliverables, plan your work week, track your finances, etc.
=TEXT( NOW()) is a highly-customizable official Google Spreadsheet function that returns the computer system date and time in a cell.
Important note: The function value is updated when your document “re-calculates” which means that, unless you make any edits to the document, you are not going to see the current date and time. However if you plan to edit your document on a regular basis (to update the task statuses, for example), this is not going to be an issue.
The awesome thing about this function is that it has plenty of available parameters that allow to format the date and time in various ways):
- See the current day, month and year: =TEXT( NOW() ; “yyyy-M-d” ) returns 2011-2-20
- See the current time (with seconds): =TEXT( NOW() ; “HH:mm:ss” ) returns 13:24:56
- See the current day of the week (and the number of the week in a month): =TEXT( NOW() ; “yyyy ‘week’ w” ) returns 2011 week 8
- See the current time and time zone: =TEXT( NOW() ; “HH:mm z” ) returns 13:24 GMT+00:00
- Even see the current era! =TEXT( NOW() ; “yyyy G GGGG” ) returns 2011 AD Anno Domini
Here’s an awesome spreadsheet listing all the available parameters as well as the examples of usage.
If you plan to publish the spreadsheet to the web, the above function won’t work as (like I have noted above), it is only updated when you edit the current spreadsheet (i.e. type/edit/delete a value in any cell). For published and rarely updated spreadsheets there is another useful function that can turn really helpful (it is undocumented but is included in the Google Spreadsheet formula builder. This is how I actually discovered it).
If you use the =GoogleClock() function in a spreadsheet, it will automatically update live whenever you view the spreadsheet (including shared viewing). Besides, if you publish a spreadsheet as a webpage, it will automatically update every 5 minutes.
The function always returns the current date and time in the following format (unless there are any parameters I am not aware of):
Note: Here are similar functions for Microsoft Excel (also updated live). Want more? Here’s a list of Google Spreadsheet tricks that are easy to remember.
Are you aware of any other time management functions in Google Spreadsheets? Please let us know!