Скачать или смотреть How to Calculate Work Hours Across Different Rates in Google Sheets

Discover how to efficiently calculate working hours with different pay rates using Excel or Google Sheets. Learn step-by-step to maximize your earnings!
---
This video is based on the question https://stackoverflow.com/q/75102421/ asked by the user 'Võ Đăng Khoa' ( https://stackoverflow.com/u/18690283/ ) and on the answer https://stackoverflow.com/a/75102453/ provided by the user 'Mask Well' ( https://stackoverflow.com/u/20895489/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How calculator time worked with 2 time different in google excel?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding How to Calculate Work Hours with Different Pay Rates in Google Sheets

Calculating your potential earnings based on different work hours can seem daunting, particularly when your pay rate changes after a specific time. If you've ever had to deal with this situation, you know it can be a juggling act trying to figure out how much you're owed for your hard work.

Imagine you have two timestamps: work in at 12/01/2023 9:00 AM and work out at 13/01/2023 6:00 AM. You have a pay structure where:

You earn 13,000 before 23:00 (11:00 PM).

You earn 15,000 after 23:00.

How do you approach calculating your total pay for this period? Let’s break down the solution step by step.

Setting Up Your Spreadsheet

First, ensure your spreadsheet is well-organized:

Cell B3: Enter your "work in" time (12/01/2023 9:00 AM).

Cell C3: Enter your "work out" time (13/01/2023 6:00 AM).

Now you can start formulating how to calculate your working hours.

The Formula to Use

You can utilize Excel's IF and AND functions to differentiate between the two pay rates. Here’s the formula that fits your scenario:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Formula

First IF Condition:

AND(C3>=B3, C3<TIME(23,0,0)): This checks if the work out time in cell C3 is greater than or equal to the work in time in cell B3 and less than 23:00.

If true, the formula calculates total time worked before 23:00 and multiplies it by 13,000.

Second IF Condition:

AND(C3>=TIME(23,0,0), C3<=TIME(24,0,0)): This checks if the work out time is between 23:00 and midnight (24:00).

If true, it calculates the time worked after 23:00 and multiplies that by 15,000.

Fallback Value:

If neither condition is met, it returns 0, meaning no hours fall under the calculated rates.

Summing It Up

After applying this formula, you will have calculated the total pay for both periods. To get a final total, simply add the results from the first and second parts of your calculations.

Conclusion

By understanding and applying the functions in this guide, you can efficiently calculate your expected earnings in Google Sheets for varying pay rates based on the time worked. This will not only aid in accurate payroll computations but also empower you with valuable skills in managing your time and income.

Now you can work confidently knowing exactly how much you should be compensated for your hours, no matter when you clock in or out!