Có một quy luật tự nhiên kỳ diệu, gần như thần bí nói rằng ba trong số những điều chúng ta mong muốn nhất—hạnh phúc, tự do và bình yên trong tâm hồn—luôn đạt được khi chúng ta trao chúng cho người khác. Cho đi để nhận lại
— Huấn luyện viên bóng rổ John Wooden
Giới thiệu
Hình 1. Quy tắc giờ tiết kiệm ánh sáng ban ngày của Hoa Kỳ. Xuân tới, Thu về. Đồng hồ thay đổi lúc 2 giờ sáng Chủ Nhật. [Nguồn. Tôi]
Gần đây, tôi gặp phải tình huống cần sửa một số giá trị ngày/giờ vì chúng không tính đến Giờ tiết kiệm ánh sáng ban ngày [DST]. Cụ thể, một số giao dịch từ Trung Quốc đã được ghi lại với giả định là thời gian bù cố định đối với Giờ chuẩn miền Trung Hoa Kỳ. Vì DST, điều này không phải lúc nào cũng đúng. Khách hàng của tôi chỉ làm việc trên Excel nên công việc được thực hiện trên Excel
Để thực hiện chỉnh sửa này, tôi cần biết liệu các giá trị ngày/giờ có xảy ra trong phạm vi ngày DST cho những năm đó hay không. Như được thảo luận bên dưới, Hoa Kỳ đã thông qua các nhiệm vụ DST hiện tại của mình vào năm 2005. Tôi chỉ cần tính hiệu chỉnh DST cho những ngày sau năm 2005, vì vậy tôi sẽ không phải lo lắng về những năm trước đó
Tiểu sử
Định nghĩa ngày
Đạo luật Chính sách Năng lượng năm 2005 đã xác định ngày của DST là
- Bắt đầu DST. Chủ nhật thứ hai của tháng ba
- kết thúc DST. Chủ nhật đầu tiên của tháng 11
Ngày Excel
Giải quyết vấn đề này cần có hai hàm ngày trong Excel
DATE[năm, tháng, ngày] Hàm này tính toán một đối tượng ngày trong Excel, là một số nguyên bằng số ngày kể từ ngày 1 tháng 1 năm 1900. WEEKDAY[đối tượng ngày tháng]Hàm này lấy một đối tượng ngày tháng và tính toán một số nguyên tương ứng với ngày trong tuần, trong đó cách đánh số mặc định là Thứ hai = 1, Thứ ba = 2, … , Chủ nhật = 7Phân tích
Cách tính ngày
Tôi sẽ minh họa phép tính bằng phép tính ngày DST mùa xuân 2021
- Tạo một đối tượng ngày cho ngày DST mùa xuân mới nhất có thể [14-Mar-2021]
- Nếu ngày 1 tháng 3 là thứ hai thì chủ nhật thứ hai của tháng 3 là ngày 14
- Tính toán Excel. mới nhất = NGÀY[2021, 3, 14]
- Tính ngày trong tuần của ngày 14 tháng 3 năm 2021 từ Chủ Nhật trước đó
- Đây chỉ đơn giản là số ngày trong tuần của ngày 14 tháng 3 năm 2021 trừ đi 1
- offset = WEEKDAY[mới nhất] - 1 [Chủ Nhật = 0, Thứ Hai = 1, …, Thứ Bảy = 6]
- SpringDST = latest -offset = DATE[2021, 3, 14] - [WEEKDAY[DATE[2021,3,14]]-1]
=14-Mar-2021
Logic tương tự có thể được sử dụng để cung cấp cho chúng tôi ngày DST mùa thu bằng cách thay thế Chủ nhật đầu tiên gần nhất có thể trong tháng 11. NGÀY[2021, 11, 7] - [NGÀY TUẦN[NGÀY[2021,11,7]-1] = 7-Tháng 11-2021
Ví dụ sổ làm việc Excel
Sổ làm việc Excel của tôi có thể được tìm thấy ở đây. Tôi đã sử dụng nó để tạo các bảng sau [Hình 2 và 3] của ngày DST mùa xuân và mùa thu, phù hợp với một bảng trực tuyến tương tự
GMT là từ viết tắt của Greenwich Meridian Time, là giờ tham chiếu cho thế giới; . " [Zulu là tên phiên âm của số 0, và số 0 dùng để chỉ kinh độ của Greenwich, Anh. ]
Bạn có thể cần chuyển đổi giờ địa phương thành GMT trong trang tính của mình. Nếu bạn luôn biết rằng thời gian sẽ được nhập theo giờ địa phương, điều này có thể được thực hiện khá dễ dàng bằng công thức. Chẳng hạn, giả sử rằng bạn đang nhập giờ địa phương vào ô B7 và bạn đang ở múi giờ Thái Bình Dương. Ở múi giờ này, bạn có thể chậm hơn bảy hoặc tám giờ so với giờ GMT, tùy thuộc vào việc giờ tiết kiệm ánh sáng ban ngày có hiệu lực hay không. Công thức sau đây sẽ điều chỉnh thời gian đã nhập trong B7 theo bảy hoặc tám giờ, tùy thuộc vào việc ngày được liên kết với thời gian có nằm trong khoảng thời gian tiết kiệm ánh sáng ban ngày hay không
=IF[AND[B7>=DATEVALUE["3/8/2009 02:00"],B19=$E$1,B19