How to Calculate Time in Google Sheets (Hours, Minutes, Seconds) | Calculate Time Difference

In this video, we will show you how to calculate time in Google Sheets.

You can calculate the duration between two timestamps. This can be done through formatting the data and using simple calculation to find out the time. For example, we have a sheet over here with the names of employees along with when they clocked in and clocked out of work. Over here, we will be first formatting the data properly before doing any calculation.

First of all, let’s start by selecting the columns containing both Time in and Time Out data. Next, let’s open up the more formats option here, and select the “Time Option”.

You can see that the data is now properly formatted.

Similarly, let’s select the Hours Worked column here, move to the formats section and select the Duration option. This is because in this column, we will be calculating the number of hours the employee has worked during the day.

Next, let’s select this cell and enter the equals to sign. Now we will select the Time out Cell reference and minus it from the time in cell reference.

Once you hit enter, you can see the number of hours worked during the day. We can simply copy and paste the formula for the remaining cells.

That’s all!

❓💬 Would you like to know about any particular Google Sheets feature? Let us know in the comments below.

#HowTech #GoogleSheets
--------------------------------------------------------------------------------------------------------------

[niklasbirkved]

Thanks, i used to this crazy conversion from a "time" to hours and minutes and then do a conversion and cacluation, this is much simpler, thanks

[jeanc.]

Ok, now sum duration of all work hours to know all these days durations. That's what I'm dying to know. What you did there is just plain intuitive.

[lifeveganized]

THANK YOU!! I was looking this up and reading about how to do this for 30 minutes before finding your video, you made this so easy!! <3

[david_sanchez]

Right but how do you get the total hours for the week? Google sheets will automatically reset the hours to 0 when it hits 24. So fi you worked 40 in a week, Google sheets interprets 40 hours as 16.

[Itsprincerara]

ok but why wouldnt you tell us about how to calculate the total at the end?

[ektad]

Thanks for the video. I tried it and it worked. We are working on shifts, the formula does not compute anymore when you jump from another day. It only computes the time/duration if the hours are on the same day.

[sandrahinde]

ok I get the calculating the hours. Now how do I add column off to the side each day with the wage?

[generector]

Extremely valuable formula: =Value(Hour(now()+time(2, 0, 0)))+value(minute(now()))/60 now() time zone adjusted, minutes as %

[MarcusCollins]

thanks this really helped. im thinking of getting an office job and this really helped

[jkbarber]

Very clear and easy to follow, thank you!

[puzzleheaddesign]

then convert to decimal? im having an issue where it rounds it up or down

[tamekabell]

THANK YOU! I was spinning my head trying to figure it out, totally different from excel

[MusicGirl]

Hello, you video is very helpful. I have a question. I have to keep track of my own hours for my job. Will this formula keep track of the minutes as well as the hours worked? For example, say I worked 1 hour and 30 minutes - will the formula also add the 30 minutes? Also, is there a way to add your wage and have Google Sheets calculate the number of hours worked by your wage? Thank you.

[BeaMonz]

How to leave out the the lunch break duration out from the total hour worked?

[snowpixxie]

Thank you for this video. it was very helpful. I have one question: what s the formula for overnight hours? For instance, I added 20:34 for time start and 05:43 for time end, but the duration of the shift was wrong and gave me a sum with - (minus). Also, if I want to apply the formula for the entire column, what is it? Thank you in advance for your reply!

[salmanchattha]

Thank you so much, in such small duration great help.
subscribed and shared

[hetoge]

Hi I'm new to this and thought I try and sort of set up my work I work Saturday to wednesday nights and sometimes a Thursday as a extra shift How can I work out my hours worked 9.68 and anytime after 9.68 is banked hours + extra shifts are paid in full at the end of each month at a different rate if pay the bank hour are paid out twice a year unless I take a day off I use my banked hours as payment = to 9.68 per day
Thanks it has helped me to understand a little better

[YessicaMontalvo-sd]

Hi i am looking for a tutorial on making google forms speak to google sheets. So basically they will sign in and out through google forms and the sheet will calculate the total hours worked can you help?

[mangafreak]

How do I calulate time durations only. for example I'm selecting few time laps from a video 1.58-3.06 and I want to calculate total min and sec contain in this time lap. how do I calculate that ?

[asokoniso]

Hi, when I enter a time-in at PM and end it into the AM, as in the shift starts at PM of the night and extends through the midnight and ends at the AM, the sheets calculates it forwards and writes it down as 20 something hours. How can I solve this problem?