Chào mừng các bạn đến với bài viết hướng dẫn tạo bảng tính Google Sheets để tính toán thời gian làm việc theo ngày.
Không quan trọng nhân viên của bạn làm việc vào ban ngày, cả ngày và đêm, cuối tuần hay làm thêm giờ, bạn chỉ cần tính toán dựa trên thời gian bắt đầu và kết thúc là làm được.
Nếu bạn đang thực hiện việc tính lương và theo dõi thời gian làm việc của nhân viên, bạn có thể tiết kiệm thời gian quý báu của mình bằng cách làm theo hướng dẫn này và sử dụng mẫu bảng tính kèm theo.
Bạn chỉ cần nhập thời gian bắt đầu công việc, thời gian kết thúc công việc và mức lương theo giờ cho cả giờ làm việc bình thường và giờ làm thêm. Các ô tương ứng được đánh dấu màu cam trong bảng tính. Còn lại thì công thức sẽ giúp bạn.
Google Sheets: Thời gian làm việc theo ngày, Điều gì đó?
Trong dữ liệu mẫu trên Google Sheets, Cột A chứa ngày và giờ bắt đầu công việc. Đó là thời gian mà nhân viên bắt đầu làm việc. Trong cột B, bạn có thể thấy ngày kết thúc công việc. Cả hai đều ở định dạng ngày và giờ.
Nhập thời gian bắt đầu công việc và thời gian kết thúc công việc dưới dạng ngày và giờ là bắt buộc để tính toán trong công việc làm ca ngày và đêm (có ngoại lệ và xem liên kết công thức tính giờ làm thêm tốt nhất). Vì thời gian bắt đầu và kết thúc công việc có thể nằm trên hai ngày khác nhau.
Ví dụ, xem hàng thứ 5 trong dữ liệu mẫu trên đây. Nhân viên bắt đầu công việc vào ngày 26/04/18 và kết thúc vào ngày 27/04/18, tức là vào ngày tiếp theo.
Dựa trên loại nhập này trong bảng thời gian của bạn trên Google Sheets, chúng ta có thể tính toán thời gian làm việc theo ngày như sau.
Thêm thông tin về cách tính toán Thời gian làm việc theo ngày trong Google Sheets
Dưới đây là dữ liệu hàng 5 và 6 trong bảng mẫu của tôi. (xem bảng mẫu số 1)
Hàng 5:
04/25/18 17:00:00 | 04/26/18 5:00:00
Hàng 6:
04/26/18 17:00:00 | 04/27/18 5:00:00
Trong ví dụ này, ngày 26/04/2018 là thứ Năm. Nhân viên đã làm tổng cộng 12 giờ vào thứ Năm (xem bảng mẫu số 2). Làm thế nào? Trong hàng số 5, thời gian làm việc của anh ta vào ngày thứ Năm là 5 giờ và trong hàng số 6 là 7 giờ. Tổng cộng 12 giờ.
Trong Google Sheets, bạn có thể tính toán loại Thời gian làm việc theo ngày này bằng một công thức đơn giản.
Làm thế nào để tính toán Thời gian làm việc theo ngày từ Thời gian bắt đầu và Thời gian kết thúc
Nếu bạn đã nhập thời gian bắt đầu và thời gian kết thúc của nhân viên vào cột A và B theo dạng dữ liệu mẫu của tôi (xem bảng mẫu số 1), bạn có thể sử dụng công thức dưới đây trong ô D2. Bạn không cần phải làm bất kỳ điều gì thêm.
Tôi biết nó khó đọc nhưng hữu ích nếu bạn tuân thủ định dạng dữ liệu của tôi đã đề cập ở trên.
={"Ngày","Tổng giờ";
query(ArrayFormula(query({int(A3:A),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col1");int(B3:B),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col2")},"Select Col1,Col2 where Col2 is not null")),"Select Col1, Sum (Col2) group by Col1 label Sum(Col2)''")}Nó sẽ trả về tổng số giờ làm việc theo ngày như bên trên (xem bảng mẫu số 2). Nhưng hãy sao chép lại tất cả dấu ngoặc kép trong công thức này để tránh lỗi phân tích cú pháp.
Bây giờ chúng ta có tổng số giờ làm việc bình thường theo ngày trong cột E. Bước kế tiếp là tính toán giờ làm việc làm thêm, giờ làm việc cuối tuần, vv từ giá trị cột E trên.
Trước đó, hãy để tôi giải thích công thức trên. Nếu bạn là người mới bắt đầu sử dụng Google Sheets hoặc chỉ muốn sử dụng mẫu mà tôi sẽ cung cấp, bạn có thể bỏ qua phần giải thích này. Vì lý do đó, tôi đã tách phần giải thích ra bằng một dòng phân cách bên dưới. Nhưng tôi khuyến khích bạn đọc tiếp để hiểu công thức làm gì.
Bạn có thể truy cập vào bảng ví dụ của tôi tại đây.Open tệp này, sau đó sao chép nó từ menu File. Điều này sẽ tạo ra một bản sao có thể chỉnh sửa của tệp.
Giải thích Công thức: Công thức tính Thời gian làm việc theo ngày trong Google Sheets
Thực sự, công thức trên thực hiện một tóm tắt theo hàng trong công thức. Công thức tìm các ngày giống nhau trong các hàng khác nhau và tổng cộng chúng. Làm thế nào?
Ví dụ, tôi sao chép lại cùng dữ liệu trên (bảng mẫu #1) trong một tab trang tính mới và nhập công thức sau vào ô C3.
Lưu ý: Nếu bạn muốn tìm hiểu tất cả các chức năng Google Sheets được đề cập trong các công thức lồng nhau trong bài viết này, vui lòng tham khảo Hướng dẫn chức năng Google Sheets của tôi.
=ArrayFormula(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),))Xem kết quả dưới đây.
Công thức này tính tổng giờ làm việc từ giờ bắt đầu (giờ vào) và giờ kết thúc (giờ ra). Không giới hạn chỉ đến vậy. Nếu ngày bắt đầu và ngày kết thúc khác nhau, công thức sẽ đơn giản chia thời gian làm việc theo đúng tỷ lệ đó.
Liên quan: Chia giờ làm việc ca đêm thành hai cột
Bạn có thể làm theo hướng dẫn trên để biết cách công thức này hoạt động. Nhưng có một điều. Đó là công thức tạo ra tổng thời gian thay vì tổng giờ.
Điều đó có nghĩa là cột C và cột D sẽ là thời gian, không phải số giờ. Chỉ có ít thay đổi trong phần cuối của công thức.
Bạn có thể đặt công thức này #2 và công thức chi tiết trong hướng dẫn đã nêu cạnh nhau và so sánh để tìm hiểu sự khác biệt.
Bây giờ trở lại với bài hướng dẫn. Công thức #2 trên là phần chính của công thức #1. Nếu tham khảo hình ảnh #4, bạn có thể thấy công thức #2 trả về hai cột (cột C và D).
Với một công thức truy vấn Google Sheets đơn giản, chúng ta có thể tách các cột như sau và có một mục đích trong đó.
Công thức #3:
=query(ArrayFormula(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),)),"Select Col1")Công thức #4:
Bạn có thể sao chép công thức số 3 ở đây. Sau đó chỉ cần thay đổi Col1 thành Col2. Vì vậy tôi không sao chép lại công thức ở đây lần nữa.
Công thức #5:
Công thức chung dưới đây là cơ bản của công thức #1 của chúng tôi. Trong đó, C2:C là công thức số 3 và D2:D là công thức số 4. Tôi có nghĩa là thay thế chúng bằng công thức số 3 và số 4 tương ứng.
ArrayFormula(query({int(A2:A),C2:C;int(B2:B),D2:D},”Select Col1,Col2 where Col2 is not null”))
Công thức phiên bản chung dưới đây là công thức thực cảnh của chúng tôi, thực hiện việc xáo trộn cột.
=ArrayFormula(query({int(A3:A),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col1");int(B3:B),query(IF(LEN(A3:A),(if(weekday(A3:A)<>WEEKDAY(B3:B),{(int(B3:B)-A3:A)*24,(B3:B-int(B3:B))*24},{((B3:B-A3:A)*24),MOD(ROW(B3:B),1)})),),"Select Col2")},"Select Col1,Col2 where Col2 is not null"))Đầu ra:
Như bạn có thể thấy, có nhiều lần xuất hiện của các ngày giống nhau trong đầu ra này. Vì vậy trong công thức cuối cùng (công thức số 1) tôi chỉ cần cộng tổng cột 2 bằng cách nhóm các ngày trong cột 1. Điều này giúp bạn tính toán thời gian làm việc theo ngày trong Google Sheets.
Chúng ta đã tính toán thời gian làm việc theo ngày. Giờ hãy xem cách tính toán thời gian làm việc bình thường và thời gian làm thêm từ đó.
Loại bỏ giờ làm thêm cuối tuần khỏi thời gian làm việc theo ngày.
Tôi sẽ hiển thị công thức trong ô G2 trước.
Công thức #6:
={"Tổng giờ bình thường";sum(ArrayFormula(IF(LEN(D3:D),if((weekday(D3:D)<>1)*(weekday(D3:D)<>7)>0,E3:E,),)))}Trong công thức này, giá trị 1 chỉ ngày Chủ nhật và 7 chỉ ngày Thứ bảy. Đây là số cuối tuần. Bạn có thể tìm hiểu thêm về ngày cuối tuần trong Hướng dẫn chức năng ngày của Google Sheets.
Công thức Google Sheets này loại bỏ các ngày cuối tuần khỏi giờ làm việc và tổng cộng tổng số giờ làm việc. Tổng cộng theo dữ liệu mẫu trên là 41 giờ.
Sau đó còn ngày cuối tuần. Tôi xem xét giờ làm việc cuối tuần như là làm thêm toàn thời gian.
Cách tính toán Giờ làm việc cuối tuần từ Thời gian bắt đầu và kết thúc
Giờ làm việc vào cuối tuần được coi là làm thêm toàn thời gian trong tính toán của tôi. Để tính toán Giờ làm việc cuối tuần, công thức mà tôi đã sử dụng trong ô H2 như sau.
Công thức #7:
={"Tổng giờ làm việc cuối tuần";sum(ArrayFormula(IF(LEN(D3:D),if((weekday(D3:D)=1)+(weekday(D3:D)=7)>0,E3:E,),)))}Và tổng cộng là 14 giờ.
Vì vậy, dựa trên hai công thức trên, tổng số giờ làm việc là;
Giờ làm việc bình thường + Giờ làm việc cuối tuần = 55 giờ.
Vì vậy bây giờ tôi chỉ trừ số giờ làm việc làm thêm khỏi số giờ làm việc bình thường (loại trừ cuối tuần vì cuối tuần đã được coi là làm thêm toàn thời gian).
Cách tính toán Giờ làm việc làm thêm từ Thời gian bắt đầu và kết thúc
Tôi đang nói về công thức trong ô I2 như sau.
Công thức #8:
={"Tổng giờ làm việc làm thêm";sum(ArrayFormula(if(IF(LEN(D3:D),if((weekday(D3:D)<>1)*(weekday(D3:D)<>7)>0,E3:E,),)>8,E3:E-8,)))}Nếu nhân viên làm việc hơn 8 giờ một ngày, tôi xem số giờ thêm là số giờ thừa. Bạn có thể thay đổi cả hai lần xuất hiện của số 8 trong công thức này để thay đổi cơ sở giờ làm thêm.
Ý nghĩa của tôi là nếu nhân viên làm việc 12 giờ trong một ngày, 8 giờ là giờ làm việc bình thường của anh ta và 4 giờ là giờ làm thêm (OT). Nhưng tôi đã loại trừ cuối tuần. Tức là tính toán trên tôi đã loại trừ thứ Bảy và Chủ nhật.
Tính toán cuối cùng:
Trong ô H6:
Tổng giờ làm bình thường – Tổng số giờ làm thêm (cả hai đều loại trừ cuối tuần) = Giờ làm vàng.
Trong ô H7:
Giờ cuối tuần + Tổng số giờ làm thêm = Tổng số giờ làm thêm.
Đây là cách tôi đã thực hiện tính toán số giờ làm bình thường và OT. Bạn có thể thay đổi dựa trên các quy tắc áp dụng trong quốc gia của bạn. Công thức số 1 vẫn không đổi. Bạn chỉ cần thay đổi các công thức được sử dụng trong ô G2, H2, I2, G6 và G7.
Hi vọng bạn đã học được cách tính toán thời gian làm việc theo ngày, giờ làm thêm, giờ làm việc ban đêm và giờ làm việc cuối tuần bằng cách sử dụng Google Sheets.
Hãy thử trải nghiệm bản sao tệp ví dụ của tôi và tạo bảng tính của riêng bạn để tính toán thời gian làm việc của nhân viên dễ dàng và hiệu quả nhé!
