Pinterest Stumbleupon Whatsapp
Ads by Google

time in google spreadsheetGoogle 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 5 Great Alternatives To Google Docs You Should Consider 5 Great Alternatives To Google Docs You Should Consider Read More , 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())

=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):

Ads by Google
  • 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.

time in google spreadsheet

=GoogleClock()

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).

time in google spreadsheet

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):

m/dd/yyyy hh:mm:ss

=GoogleClock()

Note: Here are similar functions for Microsoft Excel (also updated live).

Are you aware of any other time management functions in Google Spreadsheets? Please let us know!

  1. abhey goyal
    August 30, 2016 at 1:48 am

    can the time and date function be static for lets say each row?
    i need to know data and time of each entry and should not change with a new entry. how to do ensure that the time of previous entries dont change to current date and time

  2. Trudie von Huben
    April 24, 2016 at 11:32 pm

    The function for the correct time zone as above (=TEXT (NOW(); "HH:mm z") doesn't work. I live in New Zealand and we are one of the 'first' countries to see the day everyday, Google Docs keeps giving me what looks like an American timezone and with your formula won't give the correct GMT. Even if it did, to be honest that reporting style would be not helpful as most people are accustomed to working in their time zone and having to figure out the GMT time +/- is a drawback to the formula and makes it much less helpful.

  3. Trudie von Huben
    April 24, 2016 at 11:32 pm

    The function for the correct time zone as above (=TEXT (NOW(); "HH:mm z") doesn't work. I live in New Zealand and we are one of the 'first' countries to see the day everyday, Google Docs keeps giving me what looks like an American timezone and with your formula won't give the correct GMT. Even if it did, to be honest that reporting style would be not helpful as most people are accustomed to working in their time zone and having to figure out the GMT time +/- is a drawback to the formula and makes it much less helpful.

  4. Richard Wright
    July 1, 2015 at 10:30 pm

    Replaced by the =now() function same as Excel. Function updates when a change is made to the sheet forcing a recalc / save.

    • Richard Wright
      July 1, 2015 at 10:32 pm

      This was intended as response to JAcob's post about GoogleClock that has been replaced by now()

  5. JAcob
    April 2, 2015 at 7:45 pm

    GoogleClock does not work in the new version of spreadsheets :(

  6. Amitash
    March 6, 2011 at 11:08 am

    Nice one.. I never knew this could be done in Google Docs.

    • Ann Smarty
      March 6, 2011 at 12:33 pm

      Me neither! It was a pleasant discovery!

  7. Direct E-Cigs
    March 6, 2011 at 2:26 am

    Thanks for the tip jpot! Google Docs are great for our business as we have many remote agents and we just share the docs so everyone knows their tasks.

Leave a Reply

Your email address will not be published. Required fields are marked *