Google Sheets have several inbuilt functions to calculate time difference between two times. In this blog you will learn the most effective and commonly used functions to calculate the time difference. Let’s checkout:
Table of content:
- Problem statement
- Simple subtraction
- Using IF function
- Extract specific time unit (Example Hour or Minute or Second)
- Pro tips
- About the author
Problem statement
Let’s assume that we have “Employee Name” in column A, their “Login Time” in column B and their “Logout Time” in column C. Now we have to calculate how many hours each employee has worked on and we have to mention the result in column D.
Simple subtraction
The most easiest and simplest way to calculate the time difference between two times is using the subtraction formula. To use the subtraction formula, you need to use below mentioned formula.
As per the mentioned example, we have ‘Login time’ in column B and ‘Logout time’ in column C. Now to calculate the time difference (in hours and minutes) the formula will be:
Using IF function
Using an IF function to calculate the time difference is one the recommended option. Because there could be a chance that the employee has mentioned the wrong ‘Login time’ or ‘Logout time’. As per the below example, the employee has mentioned the ‘Login time’ as 7:25 PM but the ‘Logout time’ entered as 5:00 PM.
In such cases, you can use the IF function to calculate the time difference. To use IF function the formula will be:
Formula breakdown:
- C2 > B2: This condition will check if ‘Logout time’ is greater than ‘Login time’ or not
- C2 – B2: If first condition is TRUE, then subtract the ‘Logout time’ from ‘Login time’
- “Correct the time”: If first condition is FALSE, then show the error message as “Correct the time”
There could be a chance that the date for the ‘Login time’ is different then the date for the ‘Logout time’. In such cases you need to adjust the formula accordingly.
Extract specific time unit
Let’s assume you need to extract a specific time unit. Example you need to extract only Hours or only Minute. Google Sheets has a solution for this problem.
- HOUR Function: If you want to extract only hours from the above mentioned example, then you use the ‘HOUR’ function. To use the ‘HOUR’ function, your formula will be:
2. MINUTE Function: If you want to extract only minute then you can use the ‘Minute’ function and your formula will be:
3. SECOND Function: If you want to extract only second from the above mentioned example, then your formula be:
Pro tips
- Ensure that your time values are properly formatted as a desired time format. Example “hh:mm:ss” or “hh:mm”
- If you have time difference with multiple days, then you may need to adjust the formula accordingly
- Use same format for ‘Login time’ as well as for the ‘Logout time’
- Use data validation to ensure only valid time format has been entered into the cells
About the Author
Vishal Pooner has over 12+ years of experience in Digital Marketing and automating existing workflow using the Google AppScript. You can follow him on LinkedIn, YouTube, Instagram.
If you need our help to make productivity tools or automate existing workflows for your business. Please write an email to info@exceldose.com