Template Google Sheets Lich Lam Viec Ca Truc 2027: Shift Schedule Tu Dong Cho Nha Hang Va Benh Vien

Lap lich ca lam viec cho nhieu nhan vien voi nhieu ca khac nhau la mot trong nhung cong viec phuc tap nhat trong quan ly nhan su. Nha hang phai don bao dam du nhan vien cho gio cao diem, benh vien can ca truc 24/24, trung tam cuoc goi can ca quay vong. Google Sheets voi cac ham WORKDAY, NETWORKDAYS va cong thuc thong minh co the giai quyet bai toan nay mot cach hieu qua.

Phan 1: Thach Thuc Lap Lich Ca Truc

1.1 Do Phuc Tap Cua Bai Toan Lap Lich Ca

Lap lich ca lam viec khong don gian la phan cong "ai lam ca gi". Co rat nhieu rang buoc can xem xet:

• Luat lao dong: Lam toi da 8 tieng/ngay, 48 tieng/tuan, khong qua 200 gio tang ca/nam
• Cong bang: Phan cong ca dem, ca Tet, ca le cong bang giua cac nhan vien
• Ky nang: Ca truong phai la nguoi co ky nang cao nhat trong ca do
• Uu tien ca nhan: Mot so nhan vien xin nghi phep, di hoc them, co con nho...
• Ti le bao phu: Moi ca can du so nhan vien toi thieu theo vai tro

1.2 Van De Voi Phuong Phap Truyen Thong

Nhieu doanh nghiep van dung Excel (khong cong thuc) hoac tham chi giay bien de lap lich ca. Ket qua la:

• Mat 4-8 gio moi tuan cho viec lap va dieu chinh lich
• De xay ra loi: Hai nguoi cung ca, hoac ca khong co nguoi
• Kho tinh luong chinh xac (gio tang ca, phu cap ca dem...)
• Nhan vien khieu nai phan cong khong cong bang
• Kho dieu chinh dot xuat khi co nguoi nghi om

Phan 2: Kien Truc Template Lich Ca Truc Toan Dien

2.1 Sheet 1: Danh Sach Nhan Vien

2.2 Sheet 2: Cau Hinh Ca Lam Viec

Dinh nghia cac ca lam viec:
| Ma ca | Ten ca | Gio bat dau | Gio ket thuc | So gio | Ca dem? | Phu cap dem |
|-------|--------|------------|-------------|--------|---------|-------------|
| S | Ca sang | 06:00 | 14:00 | 8 | Khong | 0% |
| A | Ca chieu | 14:00 | 22:00 | 8 | Khong | 0% |
| N | Ca toi | 22:00 | 06:00 | 8 | Co | 30% |
| D | Ca dem | 22:00 | 08:00 | 10 | Co | 30% |
| L | Ca on dinh | 08:00 | 17:00 | 8 | Khong | 0% |
| X | Nghi (nghi phep/do) | - | - | 0 | - | - |
| O | Off theo lich | - | - | 0 | - | - |
| AL | Annual Leave | - | - | 0 | - | - |

Tong gio lam viec thuc te (tru ca dem):
=SUMPRODUCT(
  (C2:C8<"22:00")*F2:F8 +
  (C2:C8>="22:00")*(24-TIMEVALUE(C2:C8)) +
  (C2:C8<"22:00")*(D2:D8<"06:00")*TIMEVALUE(D2:D8)
)

So gio ca dem (22:00-06:00):
=IF(AND(C2="N", D2="S"), 8,
  IF(C2>="22:00",
    MIN(TIMEVALUE(D2)+IF(D2<"12:00",1,0), 6/24) - MAX(TIMEVALUE(C2), 22/24),
    MAX(0, MIN(TIMEVALUE(D2), 6/24) - 0)
  ) * 24
)

2.3 Sheet 3: Lich Lam Viec Thang (Monthly Shift Calendar)

Day la sheet chinh, hien thi lich ca cua toan bo nhan vien trong thang:

Cau truc Lich Thang:
Hang 1: Ten thang + nam (merge A1:AJ1)
Hang 2: Ten nhan vien (cot A) + so thu ngay 1-31 (cot B-AF)
Hang 3: Thu trong tuan tuong ung voi ngay

Cot A: Ma NV hoac Ten NV
Cot B tro di: Ky hieu ca (S/A/N/D/L/X/O/AL)

Vi du:
| NV | 1/3 | 2/3 | 3/3 | 4/3 | 5/3 | 6/3 | 7/3 | ...
|    | CN  | T2  | T3  | T4  | T5  | T6  | T7  | ...
|NV01| S   | S   | A   | A   | O   | O   | N   | ...
|NV02| A   | A   | O   | O   | S   | S   | A   | ...
|NV03| N   | O   | O   | S   | S   | A   | A   | ...

Cong thuc hien thi ten ngay trong tuan:
=TEXT(DATE(YEAR(B$1),MONTH(B$1),B$2), "DDD")

Conditional Formatting theo loai ca:
Ca sang (S): Mau xanh duong nhat (#BBDEFB)
Ca chieu (A): Mau cam nhat (#FFE0B2)
Ca toi/dem (N/D): Mau tim nhat (#E1BEE7)
Nghi (O/X): Mau xam nhat (#F5F5F5)
Phep nam (AL): Mau vang nhat (#FFF9C4)

Canh bao thieu nhan vien (COUNTIF theo cot):
=IF(
  COUNTIF(B3:B52, "S") < $B$MinStaff_Sang,
  "THIEU NV CA SANG!",
  ""
)

So nhan vien moi ca moi ngay:
Ca sang: =COUNTIF(B:B, "S")
Ca chieu: =COUNTIF(B:B, "A")
Ca toi: =COUNTIF(B:B, "N")

2.4 Sheet 4: Tinh Gio Lam Viec Va Tang Ca

Tong ket gio lam viec moi nhan vien trong thang:

Cong thuc tinh tong gio lam theo ma ca:
=SUMPRODUCT(
  (OFFSET('Lich Thang'!B3, MATCH(A2,'Lich Thang'!A:A,0)-3, 0, 1, 31) <> "O") *
  (OFFSET('Lich Thang'!B3, MATCH(A2,'Lich Thang'!A:A,0)-3, 0, 1, 31) <> "X") *
  (OFFSET('Lich Thang'!B3, MATCH(A2,'Lich Thang'!A:A,0)-3, 0, 1, 31) <> "AL") *
  VLOOKUP(
    OFFSET('Lich Thang'!B3, MATCH(A2,'Lich Thang'!A:A,0)-3, 0, 1, 31),
    'Cau Hinh Ca'!A:E, 5, 0
  )
)

Tinh gio tang ca (vuot 8h/ngay hoac 48h/tuan):
Gio tieu chuan: =VLOOKUP(A2, 'Danh Sach NV'!A:F, 6, 0) -- cot F: gio tieu chuan/tuan
Gio thuc te: =Tong gio theo cong thuc tren
Gio tang ca: =MAX(0, Gio thuc te - Gio tieu chuan)

Phu cap ca dem:
=SUMPRODUCT(
  (OFFSET(...) = "N") + (OFFSET(...) = "D")
) * VLOOKUP(A2, 'Danh Sach NV'!A:H, 8, 0) * 8

Tong luong co ban:
=B2 * VLOOKUP(A2, 'Danh Sach NV'!A:G, 7, 0)  -- B2: tong gio; cot G: luong/gio

Tong tang ca (150% cho gio tang ca thuong, 200% ngay le):
Gio tang ca thuong: tang_ca * luong_gio * 1.5
Gio tang ca ngay le/cuoi tuan: tang_ca_le * luong_gio * 2.0

WORKDAY function - bo qua cuoi tuan khi tinh ngay lam:
Ngay lam viec trong thang (khong ke cuoi tuan):
=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),
             EOMONTH(TODAY(), 0))

Ngay lam viec con lai trong thang:
=NETWORKDAYS(TODAY(), EOMONTH(TODAY(), 0))

2.5 Sheet 5: Log Doi Ca (Swap Ca)

Khi co nhan vien xin doi ca:
| STT | Ngay doi | NV xin doi | Ca cua NV | NV nhan doi | Ca cua NV kia | Ly do | Trang thai | Nguoi duyet |
|-----|----------|-----------|----------|------------|--------------|-------|-----------|-------------|
| 1 | 15/03/2027 | NV-001 | Ca sang | NV-005 | Ca chieu | Con om | Duyet | Quan ly |
| 2 | 20/03/2027 | NV-003 | Ca toi | NV-007 | Ca sang | Viec gia dinh | Cho duyet | |

Apps Script tu dong cap nhat lich khi doi ca duoc duyet:
function onShiftSwapApproved(row) {
  const swapSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Log Doi Ca');
  const scheduleSheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Lich Thang');
  
  const swapData = swapSheet.getRange(row, 1, 1, 9).getValues()[0];
  
  if (swapData[7] === 'Duyet') {
    const swapDate = swapData[1];
    const nv1 = swapData[2];
    const shift1 = swapData[3];
    const nv2 = swapData[4];
    const shift2 = swapData[5];
    
    // Tim va swap gia tri trong Lich Thang
    const col = swapDate.getDate() + 1; // Ngay -> so cot
    
    const row1 = findEmployeeRow(scheduleSheet, nv1);
    const row2 = findEmployeeRow(scheduleSheet, nv2);
    
    if (row1 && row2) {
      scheduleSheet.getRange(row1, col).setValue(shift2);
      scheduleSheet.getRange(row2, col).setValue(shift1);
    }
  }
}

2.6 Sheet 6: Bao Cao Cham Cong Tong Hop

Bao Cao Cham Cong Thang 3/2027:
| Ma NV | Ten NV | So ngay lam | Gio thuc te | Gio TC | Phu cap dem | Ngay phep | Tong luong |
|-------|--------|------------|-------------|--------|------------|-----------|-----------|
| NV-001 | Nguyen A | 26 | 208 | 16 | 80,000 | 0 | 5,480,000 |
| NV-002 | Tran B | 24 | 192 | 0 | 160,000 | 2 | 4,960,000 |

Tinh tong luong (bao gom tang ca va phu cap ca dem):
=B2 * 25000 + C2 * 25000 * 0.5 + D2

Trong do:
B2 = tong gio lam viec tieu chuan
C2 = gio tang ca
D2 = tong phu cap ca dem
25000 = luong co ban/gio
0.5 = he so tang ca thuong (150% = 100% + 50%)

Cau hinh cho ngay le (Quoc Khanh, Tet...):
Them sheet "Ngay Le 2027" voi danh sach ngay le
Ket hop voi COUNTIFS de check ngay tang ca co phai ngay le:
=COUNTIF('Ngay Le 2027'!A:A, EXACT_DATE)

Phan 3: Huong Dan Chi Tiet Cho Nha Hang F&B

3.1 Dac Diem Ca Lam Viec Nha Hang

Ca dac thu cho nha hang:
| Ca | Gio | Vi tri | Mo ta |
|----|-----|--------|-------|
| Ca mo | 07:00-15:00 | Phong bep, thu ngan | Chuan bi khai truong |
| Ca lunch | 10:00-15:00 | Phuc vu ban | Gio cao diem trua |
| Ca chieu | 14:00-22:00 | Phuc vu ban, thu ngan | Lien tuc buoi chieu |
| Ca dinner | 17:00-23:00 | Phuc vu ban | Gio cao diem toi |
| Ca dong | 20:00-24:00 | Bep, don dep | Dong cua |
| Ca ban dem | 22:00-08:00 | Bao ve, bep truong | |

Gio cao diem can nhieu nhan vien nhat:
Sang: 11:30 - 13:30 (cao diem com trua)
Toi: 18:00 - 20:30 (cao diem an toi)

Quy tac toi thieu theo gio cao diem (Data Validation + Alert):
Thuc hien COUNTIFS tren Lich Thang cho moi khung gio:
=IF(
  COUNTIFS('Lich Thang'!B:B,"A", ...) < MinRequiredStaff_Lunch,
  "THIEU NV GIO LUNCH!",
  "OK"
)

3.2 Lap Lich Ca Theo Quy Tac Cong Bang

Nguyen tac phan cong cong bang:
1. Dem so ca dem, ca cuoi tuan, ca le trong thang cua moi NV
2. NV nao da lam nhieu ca xau nhat tuan truoc se duoc uu tien ca tot tuan nay

Theo doi "diem cong bang" (cang thap cang duoc uu tien):
=SUMPRODUCT(
  (OFFSET('Lich Thang'!B3, ROW(A1)-1, 0, 1, 31) = "N") * 3 +  -- Ca toi: 3 diem
  (OFFSET('Lich Thang'!B3, ROW(A1)-1, 0, 1, 31) = "D") * 4 +  -- Ca dem: 4 diem
  WEEKDAY(DATE(2027,3,COLUMN(B1:AF1)-1), 2) >= 6 *             -- Cuoi tuan: +2
  (OFFSET('Lich Thang'!B3, ROW(A1)-1, 0, 1, 31) <> "O")
)

Nhan vien co diem thap nhat duoc uu tien chon lich truoc:
=RANK(B2, $B$2:$B$30, 1)  -- 1 = xep hang tang dan (thap nhat truoc)

Phan 4: Huong Dan Cho Benh Vien Va Y Te

4.1 Dac Diem Ca Truc Y Te

Ca truc benh vien/phong kham:
| Ca | Gio | So BS/Y ta can | Ghi chu |
|----|-----|---------------|---------|
| Ca sang | 06:00-14:00 | 2 BS + 4 YT | Phong kham mo cua |
| Ca chieu | 14:00-22:00 | 2 BS + 3 YT | Phong kham + cap cuu |
| Ca dem | 22:00-06:00 | 1 BS + 2 YT | Cap cuu, noi tru |

Rang buoc dac biet y te:
- Bac si khong duoc truc qua 2 ca lien tuc (16h lien tuc)
- Can 8h nghi giua 2 ca truc
- Ca truc cap cuu can BS co ky nang cap cuu
- Bao dam it nhat 1 BS chuyen khoa trong moi ca

Kiem tra rang buoc khong truc lien tuc:
=IF(
  AND(B2="N", C2="S"),  -- Ca toi -> sang lien tiep
  "VI PHAM! Thieu nghi",
  ""
)

Tren toan bo luoi (ARRAYFORMULA):
=ARRAYFORMULA(
  IF(
    AND(B2:AF2="N", C2:AG2="S"),
    "VI PHAM",
    ""
  )
)

4.2 Lich Truc On Call (Truc Ho)

On Call - Bac si truc ho (Goi den khi can):
| Ngay | BS on call | SĐT | BS du phong | SĐT du phong |
|------|-----------|-----|------------|-------------|
| 01/03 | BS. Nguyen | 0901 | BS. Tran | 0902 |
| 02/03 | BS. Le | 0903 | BS. Pham | 0904 |

Vong quay on call (tu dong dua tren danh sach BS):
=INDEX('Danh Sach NV'!B:B,
  MOD(DAY(A2)-1, COUNTA('Danh Sach NV'!B2:B20)) + 2
)

Lich on call phai cover 24/7 ma khong qua tai nhan vien:
Neu co 12 BS -> moi BS on call 1 lan/12 ngay
So ngay giua 2 lan on call: =12 ngay (cong bang cho tat ca)

Phan 5: Ham Google Sheets Chuyen Dung Cho Lich Ca

5.1 Ham WORKDAY Va NETWORKDAYS

WORKDAY(start_date, days, [holidays]) - Tinh ngay lam viec:
Ngay ket thuc sau 14 ngay lam viec (khong tinh cuoi tuan va ngay le):
=WORKDAY(A2, 14, 'Ngay Le 2027'!A:A)

NETWORKDAYS(start_date, end_date, [holidays]) - Dem ngay lam viec:
So ngay lam viec trong thang hien tai:
=NETWORKDAYS(
  DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
  EOMONTH(TODAY(), 0),
  'Ngay Le 2027'!A:A
)

So ngay lam viec giua 2 moc thoi gian:
=NETWORKDAYS(A2, B2, 'Ngay Le 2027'!A:A) - 1

NETWORKDAYS.INTL - Tuy chinh ngay nghi cuoi tuan:
Voi nha hang nghi T4 va CN:
=NETWORKDAYS.INTL(A2, B2, "0101001", 'Ngay Le 2027'!A:A)
-- Chuoi "0101001": 0=lam, 1=nghi (T2T3T4T5T6T7CN)

5.2 Tinh Gio Tang Ca Va Phu Cap Tu Dong

Gio tang ca ngay thuong (>8h/ngay):
=MAX(0, Gio_Lam_Ngay - 8)

Gio tang ca cuoi tuan (100% gio lam):
=IF(WEEKDAY(Ngay, 2) >= 6, Gio_Lam_Ngay, 0)

Gio tang ca ngay le (tinh 200%):
=IF(COUNTIF('Ngay Le'!A:A, Ngay), Gio_Lam_Ngay, 0)

Tong luong tang ca trong thang:
Tang ca thuong: =Sum_TC_Thuong * LuongCoban/8 * 1.5
Tang ca cuoi tuan: =Sum_TC_CuoiTuan * LuongCoban/8 * 2.0
Tang ca ngay le: =Sum_TC_Le * LuongCoban/8 * 3.0

Phu cap ca dem (22:00-06:00, phu cap 30%):
Gio ca dem: =SUMPRODUCT((CaType="N")*8 + (CaType="D")*8)
Phu cap: =GioLamDem * LuongGio * 0.3

Tong luong thang = Luong co ban + Tang ca + Phu cap ca dem
=B2*LuongGio + C2*LuongGio*0.5 + D2*LuongGio*0.3 + E2*LuongGio*1.5 + F2*LuongGio*2.0

Phan 6: Tu Dong Gui Lich Qua Email

Apps Script gui lich ca tuan toi cho tung nhan vien:

function sendWeeklySchedule() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const scheduleSheet = ss.getSheetByName('Lich Thang');
  const employeeSheet = ss.getSheetByName('Danh Sach NV');
  
  const employees = employeeSheet.getDataRange().getValues();
  const schedule = scheduleSheet.getDataRange().getValues();
  
  // Tinh ngay dau tuan toi
  const nextMonday = getNextMonday();
  
  for (let i = 1; i < employees.length; i++) {
    const empId = employees[i][0];
    const empName = employees[i][1];
    const empEmail = employees[i][8];
    
    if (!empEmail) continue;
    
    // Tim hang cua nhan vien trong lich
    const empRow = findRowByValue(schedule, empId, 0);
    if (!empRow) continue;
    
    // Lay lich 7 ngay toi
    const weekSchedule = [];
    for (let d = 0; d < 7; d++) {
      const date = new Date(nextMonday);
      date.setDate(date.getDate() + d);
      const col = date.getDate() + 1;
      const shift = schedule[empRow][col] || 'O';
      weekSchedule.push({ date, shift });
    }
    
    // Tao noi dung email
    let emailBody = '<h2>Lich lam viec tuan toi cua ban</h2>';
    emailBody += '<table border="1">';
    emailBody += '<tr><th>Ngay</th><th>Ca lam viec</th></tr>';
    
    weekSchedule.forEach(function(day) {
      const shiftName = getShiftName(day.shift);
      emailBody += '<tr><td>' + formatDate(day.date) + '</td>';
      emailBody += '<td>' + shiftName + '</td></tr>';
    });
    emailBody += '</table>';
    
    MailApp.sendEmail({
      to: empEmail,
      subject: 'Lich ca lam viec tuan ' + formatDate(nextMonday),
      htmlBody: emailBody
    });
  }
}

function getShiftName(code) {
  const shifts = {
    'S': 'Ca sang (06:00-14:00)',
    'A': 'Ca chieu (14:00-22:00)',
    'N': 'Ca toi (22:00-06:00)',
    'D': 'Ca dem (22:00-08:00)',
    'L': 'Ca hanh chinh (08:00-17:00)',
    'O': 'Nghi (theo lich)',
    'AL': 'Nghi phep nam'
  };
  return shifts[code] || code;
}

// Cai dat trigger chay vao 18:00 moi thu 6:
// Chu nhat chay thi gui lich cho tuan toi

Phan 7: Dashboard Quan Ly Lich Ca

Tong quan hom nay:
- Nhan vien dang o ca hien tai: =COUNTIF(HomNayCa:HomNayCa, CaHienTai)
- Nhan vien vang mat (nghi): =COUNTIF(HomNayCa:HomNayCa, "O")
- Canh bao thieu nhan vien: =IF(SoNVHienTai < MinNV, "THIEU NV!", "Du NV")

Thong ke thang:
- Tong gio tang ca toan bo: =SUM(TangCa:TangCa)
- NV lam nhieu gio nhat: =INDEX(TenNV, MATCH(MAX(TongGio), TongGio, 0))
- NV nghi nhieu nhat: =INDEX(TenNV, MATCH(MAX(NgayNghi), NgayNghi, 0))
- Ty le vang mat: =SUM(NgayNghi)/(SoNV*NgayLamViecThang)*100

Bieu do phan bo ca (Pie Chart):
Ca sang: =COUNTIF(LichThang, "S")
Ca chieu: =COUNTIF(LichThang, "A")
Ca toi: =COUNTIF(LichThang, "N")
Nghi: =COUNTIF(LichThang, "O")

Phan 8: FAQ - Cau Hoi Thuong Gap

Q: Lam sao xu ly khi co nhan vien nghi om dot xuat?

Them cot "Ly do vang mat" ben canh lich ca. Khi co nhan vien nghi om, doi ky hieu ca sang "X" (nghi dot xuat) va ghi chu "Nghi om". He thong se tu dong canh bao "thieu nhan vien" neu so NV hien tai xuong duoi nguong toi thieu. Quan ly co the xem danh sach NV co the goi vao lam them (ca nghi cua ho vao ngay do).

Q: Template co tinh dung phu cap le Tet khong?

Co, ban can tao sheet "Ngay Le 2027" voi danh sach tat ca ngay le quoc gia va ngay Tet. Cong thuc tang ca se tu dong kiem tra COUNTIF voi sheet nay va ap dung he so 300% cho ngay le. Theo Bo Luat Lao Dong VN: Ngay le + thuong nhat = 300% luong.

Q: Template ho tro bao nhieu nhan vien va bao nhieu thang?

Google Sheets co the xu ly tot den 100 nhan vien va 12 thang trong mot file. Voi nhieu hon, ban nen tach theo nam hoac tach theo bo phan. Moi thang tao mot sheet "Lich_MM_YYYY" de giu file nhe. Dung IMPORTRANGE de tong hop bao cao nam tu nhieu file thang.

Q: Lam sao nhan vien co the dang ky ca minh thich truoc khi quan ly lap lich?

Tao Google Form "Dang ky mong muon lich lam viec" de nhan vien dang ky ca mong muon truoc 2 tuan. Responses vao sheet "Yeu Cau Ca". Khi lap lich, quan ly co the xem sheet nay de co gang phan cong phu hop voi mong muon khi co the, uu tien theo thu tu yeu cau.

Ket Luan

Lap lich ca truc hieu qua la nen tang cua hoat dong kinh doanh trong nganh dich vu va y te. Voi template Google Sheets nay, ban co the:

• Lap lich thang chi trong 30-60 phut thay vi mat ca ngay
• Tu dong tinh tang ca va phu cap ca dem chinh xac 100%
• Gui lich ca tu dong cho toan bo nhan vien qua email moi tuan
• Quan ly doi ca de dang, minh bach va co audit trail
• Phat hien ngay thieu nhan vien truoc khi xay ra

Tai template mien phi tai SheetStore va biet doi viec lap lich phuc tap thanh he thong tu dong!