formualizer_eval/builtins/datetime/
date_value.rs

1//! DATEVALUE and TIMEVALUE functions for parsing date/time strings
2
3use super::serial::{date_to_serial, time_to_fraction};
4use crate::args::ArgSchema;
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, FunctionContext};
7use chrono::NaiveDate;
8use formualizer_common::{ExcelError, LiteralValue};
9use formualizer_macros::func_caps;
10
11/// DATEVALUE(date_text) - Converts a date string to serial number
12#[derive(Debug)]
13pub struct DateValueFn;
14
15impl Function for DateValueFn {
16    func_caps!(PURE);
17
18    fn name(&self) -> &'static str {
19        "DATEVALUE"
20    }
21
22    fn min_args(&self) -> usize {
23        1
24    }
25
26    fn arg_schema(&self) -> &'static [ArgSchema] {
27        use std::sync::LazyLock;
28        // Single text argument; we allow Any scalar then validate as text in impl.
29        static ONE: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| vec![ArgSchema::any()]);
30        &ONE[..]
31    }
32
33    fn eval_scalar<'a, 'b>(
34        &self,
35        args: &'a [ArgumentHandle<'a, 'b>],
36        _ctx: &dyn FunctionContext,
37    ) -> Result<LiteralValue, ExcelError> {
38        let date_text = match args[0].value()?.as_ref() {
39            LiteralValue::Text(s) => s.clone(),
40            LiteralValue::Error(e) => return Err(e.clone()),
41            other => {
42                return Err(ExcelError::new_value()
43                    .with_message(format!("DATEVALUE expects text, got {other:?}")));
44            }
45        };
46
47        // Try common date formats
48        // Excel accepts many formats, we'll support a subset
49        let formats = [
50            "%Y-%m-%d",  // 2024-01-15
51            "%m/%d/%Y",  // 01/15/2024
52            "%d/%m/%Y",  // 15/01/2024
53            "%Y/%m/%d",  // 2024/01/15
54            "%B %d, %Y", // January 15, 2024
55            "%b %d, %Y", // Jan 15, 2024
56            "%d-%b-%Y",  // 15-Jan-2024
57            "%d %B %Y",  // 15 January 2024
58        ];
59
60        for fmt in &formats {
61            if let Ok(date) = NaiveDate::parse_from_str(&date_text, fmt) {
62                return Ok(LiteralValue::Number(date_to_serial(&date)));
63            }
64        }
65
66        Err(ExcelError::new_value()
67            .with_message("DATEVALUE could not parse date text in supported formats"))
68    }
69}
70
71/// TIMEVALUE(time_text) - Converts a time string to serial number fraction
72#[derive(Debug)]
73pub struct TimeValueFn;
74
75impl Function for TimeValueFn {
76    func_caps!(PURE);
77
78    fn name(&self) -> &'static str {
79        "TIMEVALUE"
80    }
81
82    fn min_args(&self) -> usize {
83        1
84    }
85
86    fn arg_schema(&self) -> &'static [ArgSchema] {
87        use std::sync::LazyLock;
88        static ONE: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| vec![ArgSchema::any()]);
89        &ONE[..]
90    }
91
92    fn eval_scalar<'a, 'b>(
93        &self,
94        args: &'a [ArgumentHandle<'a, 'b>],
95        _ctx: &dyn FunctionContext,
96    ) -> Result<LiteralValue, ExcelError> {
97        let time_text = match args[0].value()?.as_ref() {
98            LiteralValue::Text(s) => s.clone(),
99            LiteralValue::Error(e) => return Err(e.clone()),
100            other => {
101                return Err(ExcelError::new_value()
102                    .with_message(format!("TIMEVALUE expects text, got {other:?}")));
103            }
104        };
105
106        // Try common time formats
107        let formats = [
108            "%H:%M:%S",    // 14:30:00
109            "%H:%M",       // 14:30
110            "%I:%M:%S %p", // 02:30:00 PM
111            "%I:%M %p",    // 02:30 PM
112        ];
113
114        for fmt in &formats {
115            if let Ok(time) = chrono::NaiveTime::parse_from_str(&time_text, fmt) {
116                return Ok(LiteralValue::Number(time_to_fraction(&time)));
117            }
118        }
119
120        Err(ExcelError::new_value()
121            .with_message("TIMEVALUE could not parse time text in supported formats"))
122    }
123}
124
125pub fn register_builtins() {
126    use std::sync::Arc;
127    crate::function_registry::register_function(Arc::new(DateValueFn));
128    crate::function_registry::register_function(Arc::new(TimeValueFn));
129}
130
131#[cfg(test)]
132mod tests {
133    use super::*;
134    use crate::test_workbook::TestWorkbook;
135    use formualizer_parse::parser::{ASTNode, ASTNodeType};
136    use std::sync::Arc;
137
138    fn lit(v: LiteralValue) -> ASTNode {
139        ASTNode::new(ASTNodeType::Literal(v), None)
140    }
141
142    #[test]
143    fn test_datevalue_formats() {
144        let wb = TestWorkbook::new().with_function(Arc::new(DateValueFn));
145        let ctx = wb.interpreter();
146        let f = ctx.context.get_function("", "DATEVALUE").unwrap();
147
148        // Test ISO format
149        let date_str = lit(LiteralValue::Text("2024-01-15".into()));
150        let result = f
151            .dispatch(
152                &[ArgumentHandle::new(&date_str, &ctx)],
153                &ctx.function_context(None),
154            )
155            .unwrap();
156        assert!(matches!(result, LiteralValue::Number(_)));
157
158        // Test US format
159        let date_str = lit(LiteralValue::Text("01/15/2024".into()));
160        let result = f
161            .dispatch(
162                &[ArgumentHandle::new(&date_str, &ctx)],
163                &ctx.function_context(None),
164            )
165            .unwrap();
166        assert!(matches!(result, LiteralValue::Number(_)));
167    }
168
169    #[test]
170    fn test_timevalue_formats() {
171        let wb = TestWorkbook::new().with_function(Arc::new(TimeValueFn));
172        let ctx = wb.interpreter();
173        let f = ctx.context.get_function("", "TIMEVALUE").unwrap();
174
175        // Test 24-hour format
176        let time_str = lit(LiteralValue::Text("14:30:00".into()));
177        let result = f
178            .dispatch(
179                &[ArgumentHandle::new(&time_str, &ctx)],
180                &ctx.function_context(None),
181            )
182            .unwrap();
183        match result {
184            LiteralValue::Number(n) => {
185                // 14:30 = 14.5/24 ≈ 0.604166...
186                assert!((n - 0.6041666667).abs() < 1e-9);
187            }
188            _ => panic!("TIMEVALUE should return a number"),
189        }
190
191        // Test 12-hour format
192        let time_str = lit(LiteralValue::Text("02:30 PM".into()));
193        let result = f
194            .dispatch(
195                &[ArgumentHandle::new(&time_str, &ctx)],
196                &ctx.function_context(None),
197            )
198            .unwrap();
199        match result {
200            LiteralValue::Number(n) => {
201                assert!((n - 0.6041666667).abs() < 1e-9);
202            }
203            _ => panic!("TIMEVALUE should return a number"),
204        }
205    }
206}
formualizer_eval/builtins/datetime/date_value.rs

formualizer_eval/builtins/datetime/
date_value.rs
