Do you know how to split night shift job hours into two columns? If not in this Spreadsheet tutorial you can learn that. You can refer the below screenshot 2 to get a glimpse of what I am talking about.

In night shift working, the working hours will be spread across two different days. So calculating the total working hours (shift hours time duration) may be little confusing for some of you.

First of all, you should know how to calculate shift hours time duration. Then only you can understand how to split it based on the day. Once learned, on that foundation, you can build a formula to split night shift hours into separate columns.

You May Also Like:

1. Learn all functions related to Date in Google Sheets.

2. Google Sheets Time functions.

Example: Split Night Shift Hours into Columns

Time In | Time Out | Day 1 |Day 2
4/21/2018 5:00:00 PM | 4/22/2018 8:00:00 AM | 7:00:00 | 8:00:00

See the time in and time out of an employee. His work time started on 4/21/2018 at 5 PM and ended on 4/22/2018 at 8 AM.

The split work time duration is 7 hours on 4/21/2018 and 8 hours on 4/22/2018. That means the employee worked total 15 hours. So first let me explain you how can you derive the time duration value 15.

Night Shift Hours Calculation in Google Sheets

Night shift hours calculation in google sheets is not a tough task. I am talking about finding the duration of a start time and end time. In the above example, it’s 15 hours. There is no need for you to find a formula for this.

If you stick with the date and time (time stamp like 4/21/2018 5:00:00 PM) format instead of just following the time format (5:00:00 PM), you only need to subtract the time out from time in and apply Format > Number > Duration.

Screenshot 1:

Here are some advanced tips: Convert Time Duration to Day, Hour, Minute in Google Sheets

If you understand this properly, you can move to our main topic – split the total shift hours into two columns.

The formula to Split Night Shift Job Hours into Two Columns

I’ve my data in the range A1:B. So the below formula is for the cell C1. It will populate the column C and D with 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: You should re-enter the double quotes on your sheet

Screenshot 2:

Formula Explanation:

You may find the above formula little clumsy. It’s because the above formula is an Array formula. In that, I’ve added some additional functions like ArrayFormula, Len and an extra IF logical function.

The below formula is the non-array version of the above formula to split night shift job hours into two columns.

=if(weekday(A2)<>WEEKDAY(B2),{int(B2)-A2,B2-int(B2)},{(B2-A2),MOD(ROW(B2),1)})

If you use this non-array version, you should use it in Cell C2 and then copy it to down. It can’t auto-populate the result for all the rows.

Here the highlighted MOD formula is optional. It just returns 0 instead of blank cells in column D if column D has no time duration. But it’s a must in the above Array Formula.

Let me explain this formula instead of the master array formula. So you can easily understand the logic behind the array formula to split night shift job hours into two columns.

The IF, WEEKDAY and INT are the three functions used here. Here the purpose of the function Weekday is to return a number representing the day of the week. The INT function can trim out time from a timestamp in Google Sheets and return only the date.

Similar: How to Compare Time Stamp with Normal Date in Google Sheets

Here is the generic form of the above formula.

if(weekday(time in)<>WEEKDAY(time out),{time out date-time in,time out-time out date},(time out-time in))
In this time in and time out are time stamps.

If time in and time out days are different, the if the formula would return the “Time Out” date – “Time In” duration in the first column.

In the second column, it would be the “Time Out” – “Time Out” date duration.

{time out date-time in,time out-time out date}
{int(B2)-A2,B2-int(B2)}

If both dates are the same, the if logical will return the “Time Out” – “Time In” in the first column. If you use the MOD part, in the second column the formula would return 0.

(time out-time in)
(B2-A2)

That’s all about Split Night Shift Job Hours into Two Columns in Google Sheets. Enjoy!