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:
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.
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.”
The problem with this is that you will need to paste special every single time. It is quite annoying.
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.
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:
