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

formualizer_eval/builtins/datetime/
date_value.rs
