I have a spreadsheet I use for keeping track of time for a project. I use the formula =now() to insert the current time and date. The problem with this is that the time and date will update each time I make an edit in the spreadsheet. I want it to stay that time and date all the time. Here is how to handle Now dates in Google Sheets:

Now Dates in Google Sheets this is a GIF of inserting the formula =now() and after typing hi the date updates to a the current time.

Paste Special

To prevent the =now() from changing my start time I will copy (Control C) the cell with the date and Paste Special (Control Shift V). It is important to hold down the Shift key so that the formula is stripped and only the date remains.

Frustratingly, using Paste Special strips the date formatting and leaves the time in a funky decimal that is unrecognizable to most people.

Paste Special in Google Sheets by copying the value with a formula and using the edit menu to choose paste special values only.

Format the Now Dates in Google Sheets

Use the Format menu to put the funky decimal back into date and time format. Select “Number” and “Date time.”

Now Dates in Google Sheets by inserting =now() into a cell and using the format menu to format the number.

The problem with this is that you will need to paste special every single time. It is quite annoying.

You'll have to set Now Dates in Google Sheets and format them each time you paste special.

Format the Entire Column

Click on the column indicator A to highlight all of column A, or whichever column has dates in it. Choose to format the column in the Date Time format.

Pre-formatting the column solves the funky decimal problem.

Find the Difference in Time

In one column I use =now() with Paste Special (Control Shift V) to indicate what time I started the project. In a different column I use =now() with Paste Special when I finish working on the project. However, this does not tell me how long I worked.

Subtract the Times

Using a formula to subtract the second time from the first time results in a funky decimal that does not resemble how many minutes I worked.

Format Duration

Just like you can use the Format menu to format the funky time decimal to look like date and time, you can format the difference in time. Select DURATION from the Format Numbers menu.

Highlight column C and use the Format menu to select number and duration

Want More Help with This? Become a Premium Member

Fraction of Time

The format of duration is really helpful to see how long I have been working on a project. However, if I am being paid for this time the difference in Now dates in Google Sheets does NOT reflect the fraction of time I worked.

You can NOT multiply the duration by the hourly rate.

See my previous blog post on how to calculate the fraction of time:

Now Dates in Google Sheets

For a timesheet you need to know what time it is now. Use Now Dates in Google Sheets to determine how much time has passed.

Hacking Google Classroom for Standards Based Grading

Standards Based Grading for Google Classroom users. Hack your class to allow for a better way to communicate student progress on standards.

Start Teaching with Minecraft

Increase student engagement with Minecraft Education Edition. Your students can engage with learning on almost any device, including Chromebooks. Start Teaching with Minecraft

Overwhelmed and Stressed: How to Manage and Overcome It

Join Kristi Thompson, former classroom teacher, principal, superintendent to better manage your stress and avoid burnout in education.

YES! Custom Student Reports in Schoolytics

You determine the information you want to know about students. Use hashtags in Google Classroom assignments so you can see BY STUDENT how they are performing.