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/// Returns the serial number for a calendar date from year, month, and day.
28///
29/// `DATE` normalizes out-of-range month and day values to produce a valid calendar date.
30///
31/// # Remarks
32/// - Years in the range `0..=1899` are interpreted as `1900..=3799` for Excel compatibility.
33/// - The returned serial is date-system aware and depends on the active workbook system (`1900` vs `1904`).
34/// - In the `1900` system, serial mapping preserves Excel's historical phantom `1900-02-29` behavior.
35///
36/// # Examples
37/// ```yaml,sandbox
38/// title: "Build a standard date"
39/// formula: "=DATE(2024, 1, 15)"
40/// expected: 45306
41/// ```
42///
43/// ```yaml,sandbox
44/// title: "Normalize overflowing month input"
45/// formula: "=DATE(2024, 13, 5)"
46/// expected: 45662
47/// ```
48///
49/// ```yaml,docs
50/// related:
51///   - DATEVALUE
52///   - YEAR
53///   - EDATE
54/// faq:
55///   - q: "Does DATE follow the workbook 1900/1904 date system?"
56///     a: "Yes. DATE emits a serial in the active workbook date system, so the same calendar date can map to different serials across 1900 vs 1904 mode."
57/// ```
58#[derive(Debug)]
59pub struct DateFn;
60
61/// [formualizer-docgen:schema:start]
62/// Name: DATE
63/// Type: DateFn
64/// Min args: 3
65/// Max args: 3
66/// Variadic: false
67/// Signature: DATE(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar)
68/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
69/// Caps: PURE
70/// [formualizer-docgen:schema:end]
71impl Function for DateFn {
72    func_caps!(PURE);
73
74    fn name(&self) -> &'static str {
75        "DATE"
76    }
77
78    fn min_args(&self) -> usize {
79        3
80    }
81
82    fn arg_schema(&self) -> &'static [ArgSchema] {
83        use std::sync::LazyLock;
84        // DATE(year, month, day) – all scalar, numeric lenient (allow text numbers)
85        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
86            vec![
87                ArgSchema::number_lenient_scalar(),
88                ArgSchema::number_lenient_scalar(),
89                ArgSchema::number_lenient_scalar(),
90            ]
91        });
92        &SCHEMA[..]
93    }
94
95    fn eval<'a, 'b, 'c>(
96        &self,
97        args: &'c [ArgumentHandle<'a, 'b>],
98        ctx: &dyn FunctionContext<'b>,
99    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
100        let year = coerce_to_int(&args[0])?;
101        let month = coerce_to_int(&args[1])?;
102        let day = coerce_to_int(&args[2])?;
103
104        // Excel interprets years 0-1899 as 1900-3799
105        let adjusted_year = if (0..=1899).contains(&year) {
106            year + 1900
107        } else {
108            year
109        };
110
111        let date = create_date_normalized(adjusted_year, month, day)?;
112        let serial = super::serial::date_to_serial_for(ctx.date_system(), &date);
113
114        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
115            serial,
116        )))
117    }
118}
119
120/// Returns the fractional-day serial for a time built from hour, minute, and second.
121///
122/// `TIME` normalizes overflowing and negative components by wrapping across day boundaries.
123///
124/// # Remarks
125/// - The result is always in the range `0.0..1.0` and represents only a time-of-day fraction.
126/// - Values are normalized like Excel (for example, `25` hours becomes `01:00:00`).
127/// - Time fractions are date-system independent because they do not include a date component.
128///
129/// # Examples
130/// ```yaml,sandbox
131/// title: "Create noon"
132/// formula: "=TIME(12, 0, 0)"
133/// expected: 0.5
134/// ```
135///
136/// ```yaml,sandbox
137/// title: "Wrap overflowing hour"
138/// formula: "=TIME(25, 0, 0)"
139/// expected: 0.0416666667
140/// ```
141///
142/// ```yaml,docs
143/// related:
144///   - TIMEVALUE
145///   - HOUR
146///   - NOW
147/// faq:
148///   - q: "Can TIME return values greater than 1 day?"
149///     a: "No. TIME wraps overflow and always returns a fraction in [0,1), so extra days are discarded."
150/// ```
151#[derive(Debug)]
152pub struct TimeFn;
153
154/// [formualizer-docgen:schema:start]
155/// Name: TIME
156/// Type: TimeFn
157/// Min args: 3
158/// Max args: 3
159/// Variadic: false
160/// Signature: TIME(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar)
161/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
162/// Caps: PURE
163/// [formualizer-docgen:schema:end]
164impl Function for TimeFn {
165    func_caps!(PURE);
166
167    fn name(&self) -> &'static str {
168        "TIME"
169    }
170
171    fn min_args(&self) -> usize {
172        3
173    }
174
175    fn arg_schema(&self) -> &'static [ArgSchema] {
176        use std::sync::LazyLock;
177        // TIME(hour, minute, second) – scalar numeric lenient
178        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
179            vec![
180                ArgSchema::number_lenient_scalar(),
181                ArgSchema::number_lenient_scalar(),
182                ArgSchema::number_lenient_scalar(),
183            ]
184        });
185        &SCHEMA[..]
186    }
187
188    fn eval<'a, 'b, 'c>(
189        &self,
190        args: &'c [ArgumentHandle<'a, 'b>],
191        _ctx: &dyn FunctionContext<'b>,
192    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
193        let hour = coerce_to_int(&args[0])?;
194        let minute = coerce_to_int(&args[1])?;
195        let second = coerce_to_int(&args[2])?;
196
197        // Excel normalizes time values
198        let total_seconds = hour * 3600 + minute * 60 + second;
199
200        // Handle negative time by wrapping
201        let normalized_seconds = if total_seconds < 0 {
202            let days_back = (-total_seconds - 1) / 86400 + 1;
203            total_seconds + days_back * 86400
204        } else {
205            total_seconds
206        };
207
208        // Get just the time portion (modulo full days)
209        let time_seconds = normalized_seconds % 86400;
210        let hours = (time_seconds / 3600) as u32;
211        let minutes = ((time_seconds % 3600) / 60) as u32;
212        let seconds = (time_seconds % 60) as u32;
213
214        match NaiveTime::from_hms_opt(hours, minutes, seconds) {
215            Some(time) => {
216                let fraction = time_to_fraction(&time);
217                Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
218                    fraction,
219                )))
220            }
221            None => Err(ExcelError::new_num()),
222        }
223    }
224}
225
226pub fn register_builtins() {
227    use std::sync::Arc;
228    crate::function_registry::register_function(Arc::new(DateFn));
229    crate::function_registry::register_function(Arc::new(TimeFn));
230}
231
232#[cfg(test)]
233mod tests {
234    use super::*;
235    use crate::test_workbook::TestWorkbook;
236    use formualizer_parse::parser::{ASTNode, ASTNodeType};
237    use std::sync::Arc;
238
239    fn lit(v: LiteralValue) -> ASTNode {
240        ASTNode::new(ASTNodeType::Literal(v), None)
241    }
242
243    #[test]
244    fn test_date_basic() {
245        let wb = TestWorkbook::new().with_function(Arc::new(DateFn));
246        let ctx = wb.interpreter();
247        let f = ctx.context.get_function("", "DATE").unwrap();
248
249        // DATE(2024, 1, 15)
250        let year = lit(LiteralValue::Int(2024));
251        let month = lit(LiteralValue::Int(1));
252        let day = lit(LiteralValue::Int(15));
253
254        let result = f
255            .dispatch(
256                &[
257                    ArgumentHandle::new(&year, &ctx),
258                    ArgumentHandle::new(&month, &ctx),
259                    ArgumentHandle::new(&day, &ctx),
260                ],
261                &ctx.function_context(None),
262            )
263            .unwrap()
264            .into_literal();
265
266        match result {
267            LiteralValue::Number(n) => {
268                // Should be a positive serial number
269                assert!(n > 0.0);
270                // Should be an integer (no time component)
271                assert_eq!(n.trunc(), n);
272            }
273            _ => panic!("DATE should return a number"),
274        }
275    }
276
277    #[test]
278    fn test_date_normalization() {
279        let wb = TestWorkbook::new().with_function(Arc::new(DateFn));
280        let ctx = wb.interpreter();
281        let f = ctx.context.get_function("", "DATE").unwrap();
282
283        // DATE(2024, 13, 5) should normalize to 2025-01-05
284        let year = lit(LiteralValue::Int(2024));
285        let month = lit(LiteralValue::Int(13));
286        let day = lit(LiteralValue::Int(5));
287
288        let result = f
289            .dispatch(
290                &[
291                    ArgumentHandle::new(&year, &ctx),
292                    ArgumentHandle::new(&month, &ctx),
293                    ArgumentHandle::new(&day, &ctx),
294                ],
295                &ctx.function_context(None),
296            )
297            .unwrap();
298
299        // Just verify it returns a valid number
300        assert!(matches!(result.into_literal(), LiteralValue::Number(_)));
301    }
302
303    #[test]
304    fn test_date_system_1900_vs_1904() {
305        use crate::engine::{Engine, EvalConfig};
306        use crate::interpreter::Interpreter;
307
308        // Engine with default 1900 system
309        let cfg_1900 = EvalConfig::default();
310        let eng_1900 = Engine::new(TestWorkbook::new(), cfg_1900.clone());
311        let interp_1900 = Interpreter::new(&eng_1900, "Sheet1");
312        let f = interp_1900.context.get_function("", "DATE").unwrap();
313        let y = lit(LiteralValue::Int(1904));
314        let m = lit(LiteralValue::Int(1));
315        let d = lit(LiteralValue::Int(1));
316        let args = [
317            crate::traits::ArgumentHandle::new(&y, &interp_1900),
318            crate::traits::ArgumentHandle::new(&m, &interp_1900),
319            crate::traits::ArgumentHandle::new(&d, &interp_1900),
320        ];
321        let v1900 = f
322            .dispatch(&args, &interp_1900.function_context(None))
323            .unwrap()
324            .into_literal();
325
326        // Engine with 1904 system
327        let cfg_1904 = EvalConfig {
328            date_system: crate::engine::DateSystem::Excel1904,
329            ..Default::default()
330        };
331        let eng_1904 = Engine::new(TestWorkbook::new(), cfg_1904);
332        let interp_1904 = Interpreter::new(&eng_1904, "Sheet1");
333        let f2 = interp_1904.context.get_function("", "DATE").unwrap();
334        let args2 = [
335            crate::traits::ArgumentHandle::new(&y, &interp_1904),
336            crate::traits::ArgumentHandle::new(&m, &interp_1904),
337            crate::traits::ArgumentHandle::new(&d, &interp_1904),
338        ];
339        let v1904 = f2
340            .dispatch(&args2, &interp_1904.function_context(None))
341            .unwrap()
342            .into_literal();
343
344        match (v1900, v1904) {
345            (LiteralValue::Number(a), LiteralValue::Number(b)) => {
346                // 1904-01-01 is 1462 in 1900 system, 0 in 1904 system
347                assert!((a - 1462.0).abs() < 1e-9, "expected 1462, got {a}");
348                assert!(b.abs() < 1e-9, "expected 0, got {b}");
349            }
350            other => panic!("Unexpected results: {other:?}"),
351        }
352    }
353
354    #[test]
355    fn test_time_basic() {
356        let wb = TestWorkbook::new().with_function(Arc::new(TimeFn));
357        let ctx = wb.interpreter();
358        let f = ctx.context.get_function("", "TIME").unwrap();
359
360        // TIME(12, 0, 0) = noon = 0.5
361        let hour = lit(LiteralValue::Int(12));
362        let minute = lit(LiteralValue::Int(0));
363        let second = lit(LiteralValue::Int(0));
364
365        let result = f
366            .dispatch(
367                &[
368                    ArgumentHandle::new(&hour, &ctx),
369                    ArgumentHandle::new(&minute, &ctx),
370                    ArgumentHandle::new(&second, &ctx),
371                ],
372                &ctx.function_context(None),
373            )
374            .unwrap()
375            .into_literal();
376
377        match result {
378            LiteralValue::Number(n) => {
379                assert!((n - 0.5).abs() < 1e-10);
380            }
381            _ => panic!("TIME should return a number"),
382        }
383    }
384
385    #[test]
386    fn test_time_normalization() {
387        let wb = TestWorkbook::new().with_function(Arc::new(TimeFn));
388        let ctx = wb.interpreter();
389        let f = ctx.context.get_function("", "TIME").unwrap();
390
391        // TIME(25, 0, 0) = 1:00 AM next day = 1/24
392        let hour = lit(LiteralValue::Int(25));
393        let minute = lit(LiteralValue::Int(0));
394        let second = lit(LiteralValue::Int(0));
395
396        let result = f
397            .dispatch(
398                &[
399                    ArgumentHandle::new(&hour, &ctx),
400                    ArgumentHandle::new(&minute, &ctx),
401                    ArgumentHandle::new(&second, &ctx),
402                ],
403                &ctx.function_context(None),
404            )
405            .unwrap()
406            .into_literal();
407
408        match result {
409            LiteralValue::Number(n) => {
410                // Should wrap to 1:00 AM = 1/24
411                assert!((n - 1.0 / 24.0).abs() < 1e-10);
412            }
413            _ => panic!("TIME should return a number"),
414        }
415    }
416}
formualizer_eval/builtins/datetime/date_time.rs

formualizer_eval/builtins/datetime/
date_time.rs
