formualizer_eval/builtins/datetime/
serial.rs

1//! Excel serial date system with 1900 leap year bug compatibility
2
3use chrono::{NaiveDate, NaiveDateTime, NaiveTime, Timelike};
4use formualizer_common::ExcelError;
5
6use crate::engine::DateSystem;
7
8// Excel's serial date system:
9// Serial 1 = 1900-01-01
10// Serial 60 = 1900-02-29 (doesn't exist, but Excel thinks it does - leap year bug)
11// Serial 61 = 1900-03-01
12// Implementation approach:
13//   Base date = 1899-12-31 (Excel serial 1 => 1900-01-01 has a one-day diff from base)
14//   Phantom day: Excel treats 1900-02-29 as serial 60 (non-existent). For serial->date we:
15//     serial < 60:  date = base + serial days
16//     serial == 60: date = 1900-02-28 (we do NOT invent an impossible date object)
17//     serial > 60:  date = base + (serial - 1) days (skip phantom)
18//   For date->serial we compute diff_days = date - base, then:
19//     if date >= 1900-03-01 add 1 to account for phantom day
20//     else use diff_days directly.
21// This matches Excel's mapping: 59 => 1900-02-28, 60 => (displays 29) we surface 28, 61 => 1900-03-01.
22
23const EXCEL_BASE_YEAR: i32 = 1899;
24const EXCEL_BASE_MONTH: u32 = 12;
25const EXCEL_BASE_DAY: u32 = 31;
26
27/// Convert Excel serial number to date
28/// Handles the 1900 leap year bug where Excel incorrectly treats 1900 as a leap year
29pub fn serial_to_date(serial: f64) -> Result<NaiveDate, ExcelError> {
30    let serial_int = serial.trunc();
31    if serial_int < 0.0 {
32        return Err(ExcelError::new_num());
33    }
34    let serial_int = serial_int as i64; // safe now
35
36    // Handle phantom day (serial 60) explicitly
37    if serial_int == 60 {
38        return Ok(NaiveDate::from_ymd_opt(1900, 2, 28).unwrap());
39    }
40
41    let base = NaiveDate::from_ymd_opt(EXCEL_BASE_YEAR, EXCEL_BASE_MONTH, EXCEL_BASE_DAY)
42        .ok_or_else(ExcelError::new_num)?;
43
44    // serial < 60: offset = serial
45    // serial > 60: offset = serial - 1 (skip phantom day)
46    let offset = if serial_int < 60 {
47        serial_int
48    } else {
49        serial_int - 1
50    };
51
52    base.checked_add_signed(chrono::TimeDelta::days(offset))
53        .ok_or_else(ExcelError::new_num)
54}
55
56/// Convert date to Excel serial number
57/// Handles the 1900 leap year bug
58pub fn date_to_serial(date: &NaiveDate) -> f64 {
59    let base = NaiveDate::from_ymd_opt(EXCEL_BASE_YEAR, EXCEL_BASE_MONTH, EXCEL_BASE_DAY).unwrap();
60    let diff = (*date - base).num_days(); // 1900-01-01 => 1
61    let serial = if *date >= NaiveDate::from_ymd_opt(1900, 3, 1).unwrap() {
62        diff + 1 // account for phantom Feb 29
63    } else {
64        diff
65    };
66    serial as f64
67}
68
69/// Convert Excel serial number to datetime
70/// The fractional part represents time of day
71pub fn serial_to_datetime(serial: f64) -> Result<NaiveDateTime, ExcelError> {
72    let date = serial_to_date(serial)?;
73    let time_fraction = serial.fract();
74
75    // Convert fraction to seconds (24 hours * 60 minutes * 60 seconds = 86400 seconds)
76    let total_seconds = (time_fraction * 86400.0).round() as u32;
77    let hours = total_seconds / 3600;
78    let minutes = (total_seconds % 3600) / 60;
79    let seconds = total_seconds % 60;
80
81    let time = NaiveTime::from_hms_opt(hours.min(23), minutes.min(59), seconds.min(59))
82        .ok_or_else(ExcelError::new_num)?;
83
84    Ok(NaiveDateTime::new(date, time))
85}
86
87/// Convert datetime to Excel serial number
88pub fn datetime_to_serial(datetime: &NaiveDateTime) -> f64 {
89    let date_serial = date_to_serial(&datetime.date());
90    let time_fraction = time_to_fraction(&datetime.time());
91    date_serial + time_fraction
92}
93
94// ───────── Date-system aware variants (1900 vs 1904) ─────────
95
96const EXCEL_1904_EPOCH: NaiveDate = NaiveDate::from_ymd_opt(1904, 1, 1).unwrap();
97
98/// Convert a date to Excel serial according to the provided date system.
99pub fn date_to_serial_for(system: DateSystem, date: &NaiveDate) -> f64 {
100    match system {
101        DateSystem::Excel1900 => date_to_serial(date),
102        DateSystem::Excel1904 => (*date - EXCEL_1904_EPOCH).num_days() as f64,
103    }
104}
105
106/// Convert a datetime to Excel serial according to the provided date system.
107pub fn datetime_to_serial_for(system: DateSystem, dt: &NaiveDateTime) -> f64 {
108    match system {
109        DateSystem::Excel1900 => datetime_to_serial(dt),
110        DateSystem::Excel1904 => {
111            let days = (dt.date() - EXCEL_1904_EPOCH).num_days() as f64;
112            let frac = time_to_fraction(&dt.time());
113            days + frac
114        }
115    }
116}
117
118/// Convert a serial to datetime according to the provided date system.
119pub fn serial_to_datetime_for(
120    system: DateSystem,
121    serial: f64,
122) -> Result<NaiveDateTime, ExcelError> {
123    match system {
124        DateSystem::Excel1900 => serial_to_datetime(serial),
125        DateSystem::Excel1904 => {
126            if serial.is_nan() || serial.is_infinite() {
127                return Err(ExcelError::new_num());
128            }
129            let days = serial.trunc() as i64;
130            let date = EXCEL_1904_EPOCH
131                .checked_add_signed(chrono::TimeDelta::days(days))
132                .ok_or_else(ExcelError::new_num)?;
133            let time_fraction = serial.fract();
134            let total_seconds = (time_fraction * 86400.0).round() as u32;
135            let hours = total_seconds / 3600;
136            let minutes = (total_seconds % 3600) / 60;
137            let seconds = total_seconds % 60;
138            let time = NaiveTime::from_hms_opt(hours.min(23), minutes.min(59), seconds.min(59))
139                .ok_or_else(ExcelError::new_num)?;
140            Ok(NaiveDateTime::new(date, time))
141        }
142    }
143}
144
145/// Convert time to fractional day (0.0 to 0.999...)
146pub fn time_to_fraction(time: &NaiveTime) -> f64 {
147    let total_seconds =
148        time.hour() as f64 * 3600.0 + time.minute() as f64 * 60.0 + time.second() as f64;
149    total_seconds / 86400.0
150}
151
152/// Create a date from year, month, day with Excel normalization
153/// Excel normalizes out-of-range values (e.g., month 13 becomes next January)
154pub fn create_date_normalized(year: i32, month: i32, day: i32) -> Result<NaiveDate, ExcelError> {
155    // Normalize month and adjust year
156    let total_months = (year * 12) + month - 1;
157    let normalized_year = total_months / 12;
158    let normalized_month = (total_months % 12) + 1;
159
160    // Create a temporary date with day 1 to handle month boundaries
161    let temp_date = NaiveDate::from_ymd_opt(normalized_year, normalized_month as u32, 1)
162        .ok_or_else(ExcelError::new_num)?;
163
164    // Add the days (minus 1 because we started at day 1)
165    temp_date
166        .checked_add_signed(chrono::TimeDelta::days((day - 1) as i64))
167        .ok_or_else(ExcelError::new_num)
168}
169
170#[cfg(test)]
171mod tests {
172    use super::*;
173
174    #[test]
175    fn test_serial_to_date_basic() {
176        // Serial 1 = 1900-01-01
177        let date = serial_to_date(1.0).unwrap();
178        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 1, 1).unwrap());
179
180        // Serial 2 = 1900-01-02
181        let date = serial_to_date(2.0).unwrap();
182        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 1, 2).unwrap());
183    }
184
185    #[test]
186    fn test_leap_year_bug() {
187        // Serial 59 = 1900-02-28
188        let date = serial_to_date(59.0).unwrap();
189        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 2, 28).unwrap());
190
191        // Serial 60 = 1900-02-29 (doesn't exist in reality, but Excel treats it as Feb 28)
192        let date = serial_to_date(60.0).unwrap();
193        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 2, 28).unwrap());
194
195        // Serial 61 = 1900-03-01
196        let date = serial_to_date(61.0).unwrap();
197        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 3, 1).unwrap());
198    }
199
200    #[test]
201    fn test_date_to_serial() {
202        // 1900-01-01 = Serial 1
203        let date = NaiveDate::from_ymd_opt(1900, 1, 1).unwrap();
204        assert_eq!(date_to_serial(&date), 1.0);
205
206        // 1900-02-28 = Serial 59
207        let date = NaiveDate::from_ymd_opt(1900, 2, 28).unwrap();
208        assert_eq!(date_to_serial(&date), 59.0);
209
210        // 1900-03-01 = Serial 61 (accounting for leap year bug)
211        let date = NaiveDate::from_ymd_opt(1900, 3, 1).unwrap();
212        assert_eq!(date_to_serial(&date), 61.0);
213    }
214
215    #[test]
216    fn test_time_fraction() {
217        // Noon = 0.5
218        let time = NaiveTime::from_hms_opt(12, 0, 0).unwrap();
219        assert!((time_to_fraction(&time) - 0.5).abs() < 1e-10);
220
221        // 6 AM = 0.25
222        let time = NaiveTime::from_hms_opt(6, 0, 0).unwrap();
223        assert!((time_to_fraction(&time) - 0.25).abs() < 1e-10);
224    }
225
226    #[test]
227    fn test_date_normalization() {
228        // Month 13 becomes next January
229        let date = create_date_normalized(2024, 13, 5).unwrap();
230        assert_eq!(date, NaiveDate::from_ymd_opt(2025, 1, 5).unwrap());
231
232        // Negative month
233        let date = create_date_normalized(2024, 0, 15).unwrap();
234        assert_eq!(date, NaiveDate::from_ymd_opt(2023, 12, 15).unwrap());
235    }
236}
formualizer_eval/builtins/datetime/serial.rs

formualizer_eval/builtins/datetime/
serial.rs
