Do you want to learn how to split night shift job hours into two columns in Google Sheets? Look no further, because in this tutorial, I will show you exactly how to do that. Just imagine the convenience of having your work hours divided into two separate days without any confusion. It’s simpler than you think!
Night Shift Working, the Challenge
Working night shifts can be a bit tricky when it comes to calculating the total working hours. Since the working hours are spread across two different days, finding the accurate shift hour duration can be confusing for some. But fear not, because I’m about to reveal the secret formula that will solve this problem for you.
Night Shift Hours Calculation in Google Sheets
Calculating night shift hours in Google Sheets is not as tough as it seems. All you need to do is follow a simple subtraction formula. Instead of just using the time format (e.g., 5:00 PM), make sure to include the date and time stamp format (e.g., 4/21/2018 5:00:00 PM). By subtracting the “time out” from the “time in,” you can easily find the duration of the shift hours. Don’t forget to format it as “Number” and then select “Duration” to get the desired result.
Splitting Night Shift Hours into Two Columns
Now that you understand how to calculate the total shift hours, let’s move on to the main topic: splitting the shift hours into two columns. By using a simple formula, you can automatically split the shift hours into separate columns.
The formula I’ve provided below is for cell C1. It will populate columns C and D with the respective time durations.
={“Day 1″,”Day 2”;ArrayFormula(IF(LEN(A2:A),(if(weekday(A2:A)<>WEEKDAY(B2:B),{int(B2:B)-A2:A,B2:B-int(B2:B)},{(B2:B-A2:A),MOD(ROW(B2:B),1)})),))}Note: Double quotes should be re-entered in your sheet.
Let me explain the formula to you in a non-array version, which can be used in cell C2 and then copied down to populate the result for all rows:
=if(weekday(A2)<>WEEKDAY(B2),{int(B2)-A2,B2-int(B2)},{(B2-A2),MOD(ROW(B2),1)})Here, the highlighted MOD formula is optional. It returns 0 instead of blank cells in column D if there is no time duration. However, it is necessary in the array formula.
The formula uses the IF, WEEKDAY, and INT functions. The WEEKDAY function returns the day of the week in number format, while the INT function removes the time from a timestamp and returns only the date.
To summarize the formula:
if(weekday(time in)<>WEEKDAY(time out),{time out-date time in,time out-time out date},(time out-time in))If the dates of the “time in” and “time out” are different, the formula will return the duration between the “time out” date and the “time in” in the first column. In the second column, it will show the duration between the “time out” and the “time out” date.
{time out-date time in,time out-time out date} {int(B2)-A2,B2-int(B2)}If both dates are the same, the formula will return the duration between the “time out” and the “time in” in the first column. If you use the MOD part, it will display 0 in the second column.
(time out-time in) (B2-A2)And that’s all there is to it! Now you can easily split night shift job hours into two columns in Google Sheets.
Enjoy the convenience and accuracy of having your shift hours neatly organized. To learn more about Google Sheets and its powerful features, check out Crawlan.com for more insightful tutorials and tips.
Remember, organizing your work hours has never been easier with Google Sheets!
