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

formualizer_eval/builtins/datetime/
date_time.rs
