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.
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:
Google
Classroom
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.
Gamification
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
Guest Post
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.
Classroom
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.
