formualizer_eval/builtins/datetime/
date_time.rs

1//! DATE and TIME functions
2
3use super::serial::{create_date_normalized, time_to_fraction};
4use crate::args::ArgSchema;
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, FunctionContext};
7use chrono::NaiveTime;
8use formualizer_common::{ExcelError, LiteralValue};
9use formualizer_macros::func_caps;
10
11fn coerce_to_int(arg: &ArgumentHandle) -> Result<i32, ExcelError> {
12    let v = arg.value()?;
13    match v.as_ref() {
14        LiteralValue::Int(i) => Ok(*i as i32),
15        LiteralValue::Number(f) => Ok(f.trunc() as i32),
16        LiteralValue::Text(s) => s.parse::<f64>().map(|f| f.trunc() as i32).map_err(|_| {
17            ExcelError::new_value().with_message("DATE/TIME argument is not a valid number")
18        }),
19        LiteralValue::Boolean(b) => Ok(if *b { 1 } else { 0 }),
20        LiteralValue::Empty => Ok(0),
21        LiteralValue::Error(e) => Err(e.clone()),
22        _ => Err(ExcelError::new_value()
23            .with_message("DATE/TIME expects numeric or text-numeric arguments")),
24    }
25}
26
27/// DATE(year, month, day) - Creates a date serial number
28#[derive(Debug)]
29pub struct DateFn;
30
31impl Function for DateFn {
32    func_caps!(PURE);
33
34    fn name(&self) -> &'static str {
35        "DATE"
36    }
37
38    fn min_args(&self) -> usize {
39        3
40    }
41
42    fn arg_schema(&self) -> &'static [ArgSchema] {
43        use std::sync::LazyLock;
44        // DATE(year, month, day) – all scalar, numeric lenient (allow text numbers)
45        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
46            vec![
47                ArgSchema::number_lenient_scalar(),
48                ArgSchema::number_lenient_scalar(),
49                ArgSchema::number_lenient_scalar(),
50            ]
51        });
52        &SCHEMA[..]
53    }
54
55    fn eval_scalar<'a, 'b>(
56        &self,
57        args: &'a [ArgumentHandle<'a, 'b>],
58        ctx: &dyn FunctionContext,
59    ) -> Result<LiteralValue, ExcelError> {
60        let year = coerce_to_int(&args[0])?;
61        let month = coerce_to_int(&args[1])?;
62        let day = coerce_to_int(&args[2])?;
63
64        // Excel interprets years 0-1899 as 1900-3799
65        let adjusted_year = if (0..=1899).contains(&year) {
66            year + 1900
67        } else {
68            year
69        };
70
71        let date = create_date_normalized(adjusted_year, month, day)?;
72        let serial = super::serial::date_to_serial_for(ctx.date_system(), &date);
73
74        Ok(LiteralValue::Number(serial))
75    }
76}
77
78/// TIME(hour, minute, second) - Creates a time serial number (fraction of day)
79#[derive(Debug)]
80pub struct TimeFn;
81
82impl Function for TimeFn {
83    func_caps!(PURE);
84
85    fn name(&self) -> &'static str {
86        "TIME"
87    }
88
89    fn min_args(&self) -> usize {
90        3
91    }
92
93    fn arg_schema(&self) -> &'static [ArgSchema] {
94        use std::sync::LazyLock;
95        // TIME(hour, minute, second) – scalar numeric lenient
96        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
97            vec![
98                ArgSchema::number_lenient_scalar(),
99                ArgSchema::number_lenient_scalar(),
100                ArgSchema::number_lenient_scalar(),
101            ]
102        });
103        &SCHEMA[..]
104    }
105
106    fn eval_scalar<'a, 'b>(
107        &self,
108        args: &'a [ArgumentHandle<'a, 'b>],
109        _ctx: &dyn FunctionContext,
110    ) -> Result<LiteralValue, ExcelError> {
111        let hour = coerce_to_int(&args[0])?;
112        let minute = coerce_to_int(&args[1])?;
113        let second = coerce_to_int(&args[2])?;
114
115        // Excel normalizes time values
116        let total_seconds = hour * 3600 + minute * 60 + second;
117
118        // Handle negative time by wrapping
119        let normalized_seconds = if total_seconds < 0 {
120            let days_back = (-total_seconds - 1) / 86400 + 1;
121            total_seconds + days_back * 86400
122        } else {
123            total_seconds
124        };
125
126        // Get just the time portion (modulo full days)
127        let time_seconds = normalized_seconds % 86400;
128        let hours = (time_seconds / 3600) as u32;
129        let minutes = ((time_seconds % 3600) / 60) as u32;
130        let seconds = (time_seconds % 60) as u32;
131
132        match NaiveTime::from_hms_opt(hours, minutes, seconds) {
133            Some(time) => {
134                let fraction = time_to_fraction(&time);
135                Ok(LiteralValue::Number(fraction))
136            }
137            None => Err(ExcelError::new_num()),
138        }
139    }
140}
141
142pub fn register_builtins() {
143    use std::sync::Arc;
144    crate::function_registry::register_function(Arc::new(DateFn));
145    crate::function_registry::register_function(Arc::new(TimeFn));
146}
147
148#[cfg(test)]
149mod tests {
150    use super::*;
151    use crate::test_workbook::TestWorkbook;
152    use formualizer_parse::parser::{ASTNode, ASTNodeType};
153    use std::sync::Arc;
154
155    fn lit(v: LiteralValue) -> ASTNode {
156        ASTNode::new(ASTNodeType::Literal(v), None)
157    }
158
159    #[test]
160    fn test_date_basic() {
161        let wb = TestWorkbook::new().with_function(Arc::new(DateFn));
162        let ctx = wb.interpreter();
163        let f = ctx.context.get_function("", "DATE").unwrap();
164
165        // DATE(2024, 1, 15)
166        let year = lit(LiteralValue::Int(2024));
167        let month = lit(LiteralValue::Int(1));
168        let day = lit(LiteralValue::Int(15));
169
170        let result = f
171            .dispatch(
172                &[
173                    ArgumentHandle::new(&year, &ctx),
174                    ArgumentHandle::new(&month, &ctx),
175                    ArgumentHandle::new(&day, &ctx),
176                ],
177                &ctx.function_context(None),
178            )
179            .unwrap();
180
181        match result {
182            LiteralValue::Number(n) => {
183                // Should be a positive serial number
184                assert!(n > 0.0);
185                // Should be an integer (no time component)
186                assert_eq!(n.trunc(), n);
187            }
188            _ => panic!("DATE should return a number"),
189        }
190    }
191
192    #[test]
193    fn test_date_normalization() {
194        let wb = TestWorkbook::new().with_function(Arc::new(DateFn));
195        let ctx = wb.interpreter();
196        let f = ctx.context.get_function("", "DATE").unwrap();
197
198        // DATE(2024, 13, 5) should normalize to 2025-01-05
199        let year = lit(LiteralValue::Int(2024));
200        let month = lit(LiteralValue::Int(13));
201        let day = lit(LiteralValue::Int(5));
202
203        let result = f
204            .dispatch(
205                &[
206                    ArgumentHandle::new(&year, &ctx),
207                    ArgumentHandle::new(&month, &ctx),
208                    ArgumentHandle::new(&day, &ctx),
209                ],
210                &ctx.function_context(None),
211            )
212            .unwrap();
213
214        // Just verify it returns a valid number
215        assert!(matches!(result, LiteralValue::Number(_)));
216    }
217
218    #[test]
219    fn test_date_system_1900_vs_1904() {
220        use crate::engine::{Engine, EvalConfig};
221        use crate::interpreter::Interpreter;
222
223        // Engine with default 1900 system
224        let cfg_1900 = EvalConfig::default();
225        let eng_1900 = Engine::new(TestWorkbook::new(), cfg_1900.clone());
226        let interp_1900 = Interpreter::new(&eng_1900, "Sheet1");
227        let f = interp_1900.context.get_function("", "DATE").unwrap();
228        let y = lit(LiteralValue::Int(1904));
229        let m = lit(LiteralValue::Int(1));
230        let d = lit(LiteralValue::Int(1));
231        let args = [
232            crate::traits::ArgumentHandle::new(&y, &interp_1900),
233            crate::traits::ArgumentHandle::new(&m, &interp_1900),
234            crate::traits::ArgumentHandle::new(&d, &interp_1900),
235        ];
236        let v1900 = f
237            .dispatch(&args, &interp_1900.function_context(None))
238            .unwrap();
239
240        // Engine with 1904 system
241        let cfg_1904 = EvalConfig {
242            date_system: crate::engine::DateSystem::Excel1904,
243            ..Default::default()
244        };
245        let eng_1904 = Engine::new(TestWorkbook::new(), cfg_1904);
246        let interp_1904 = Interpreter::new(&eng_1904, "Sheet1");
247        let f2 = interp_1904.context.get_function("", "DATE").unwrap();
248        let args2 = [
249            crate::traits::ArgumentHandle::new(&y, &interp_1904),
250            crate::traits::ArgumentHandle::new(&m, &interp_1904),
251            crate::traits::ArgumentHandle::new(&d, &interp_1904),
252        ];
253        let v1904 = f2
254            .dispatch(&args2, &interp_1904.function_context(None))
255            .unwrap();
256
257        match (v1900, v1904) {
258            (LiteralValue::Number(a), LiteralValue::Number(b)) => {
259                // 1904-01-01 is 1462 in 1900 system, 0 in 1904 system
260                assert!((a - 1462.0).abs() < 1e-9, "expected 1462, got {a}");
261                assert!(b.abs() < 1e-9, "expected 0, got {b}");
262            }
263            other => panic!("Unexpected results: {other:?}"),
264        }
265    }
266
267    #[test]
268    fn test_time_basic() {
269        let wb = TestWorkbook::new().with_function(Arc::new(TimeFn));
270        let ctx = wb.interpreter();
271        let f = ctx.context.get_function("", "TIME").unwrap();
272
273        // TIME(12, 0, 0) = noon = 0.5
274        let hour = lit(LiteralValue::Int(12));
275        let minute = lit(LiteralValue::Int(0));
276        let second = lit(LiteralValue::Int(0));
277
278        let result = f
279            .dispatch(
280                &[
281                    ArgumentHandle::new(&hour, &ctx),
282                    ArgumentHandle::new(&minute, &ctx),
283                    ArgumentHandle::new(&second, &ctx),
284                ],
285                &ctx.function_context(None),
286            )
287            .unwrap();
288
289        match result {
290            LiteralValue::Number(n) => {
291                assert!((n - 0.5).abs() < 1e-10);
292            }
293            _ => panic!("TIME should return a number"),
294        }
295    }
296
297    #[test]
298    fn test_time_normalization() {
299        let wb = TestWorkbook::new().with_function(Arc::new(TimeFn));
300        let ctx = wb.interpreter();
301        let f = ctx.context.get_function("", "TIME").unwrap();
302
303        // TIME(25, 0, 0) = 1:00 AM next day = 1/24
304        let hour = lit(LiteralValue::Int(25));
305        let minute = lit(LiteralValue::Int(0));
306        let second = lit(LiteralValue::Int(0));
307
308        let result = f
309            .dispatch(
310                &[
311                    ArgumentHandle::new(&hour, &ctx),
312                    ArgumentHandle::new(&minute, &ctx),
313                    ArgumentHandle::new(&second, &ctx),
314                ],
315                &ctx.function_context(None),
316            )
317            .unwrap();
318
319        match result {
320            LiteralValue::Number(n) => {
321                // Should wrap to 1:00 AM = 1/24
322                assert!((n - 1.0 / 24.0).abs() < 1e-10);
323            }
324            _ => panic!("TIME should return a number"),
325        }
326    }
327}
formualizer_eval/builtins/datetime/date_time.rs

formualizer_eval/builtins/datetime/
date_time.rs
