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/// Parses a date string and returns its date serial number.
12///
13/// # Remarks
14/// - Accepted formats are a fixed supported subset (for example `YYYY-MM-DD`, `MM/DD/YYYY`, and month-name forms).
15/// - Parsing is not locale-driven; ambiguous text may parse differently than Excel locales.
16/// - Output uses Excel 1900 serial mapping and does not currently switch to workbook `1904` mode.
17///
18/// # Examples
19/// ```yaml,sandbox
20/// title: "Parse ISO date"
21/// formula: '=DATEVALUE("2024-01-15")'
22/// expected: 45306
23/// ```
24///
25/// ```yaml,sandbox
26/// title: "Parse month-name date"
27/// formula: '=DATEVALUE("Jan 15, 2024")'
28/// expected: 45306
29/// ```
30///
31/// ```yaml,docs
32/// related:
33///   - DATE
34///   - TIMEVALUE
35///   - VALUE
36/// faq:
37///   - q: "Why can DATEVALUE disagree with locale-specific Excel parsing?"
38///     a: "This implementation uses a fixed set of accepted formats instead of workbook locale settings, so ambiguous text may parse differently."
39/// ```
40#[derive(Debug)]
41pub struct DateValueFn;
42
43/// [formualizer-docgen:schema:start]
44/// Name: DATEVALUE
45/// Type: DateValueFn
46/// Min args: 1
47/// Max args: 1
48/// Variadic: false
49/// Signature: DATEVALUE(arg1: any@scalar)
50/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
51/// Caps: PURE
52/// [formualizer-docgen:schema:end]
53impl Function for DateValueFn {
54    func_caps!(PURE);
55
56    fn name(&self) -> &'static str {
57        "DATEVALUE"
58    }
59
60    fn min_args(&self) -> usize {
61        1
62    }
63
64    fn arg_schema(&self) -> &'static [ArgSchema] {
65        use std::sync::LazyLock;
66        // Single text argument; we allow Any scalar then validate as text in impl.
67        static ONE: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| vec![ArgSchema::any()]);
68        &ONE[..]
69    }
70
71    fn eval<'a, 'b, 'c>(
72        &self,
73        args: &'c [ArgumentHandle<'a, 'b>],
74        _ctx: &dyn FunctionContext<'b>,
75    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
76        let date_text = match args[0].value()?.into_literal() {
77            LiteralValue::Text(s) => s,
78            LiteralValue::Error(e) => {
79                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
80            }
81            other => {
82                return Err(ExcelError::new_value()
83                    .with_message(format!("DATEVALUE expects text, got {other:?}")));
84            }
85        };
86
87        // Try common date formats
88        // Excel accepts many formats, we'll support a subset
89        let formats = [
90            "%Y-%m-%d",  // 2024-01-15
91            "%m/%d/%Y",  // 01/15/2024
92            "%d/%m/%Y",  // 15/01/2024
93            "%Y/%m/%d",  // 2024/01/15
94            "%B %d, %Y", // January 15, 2024
95            "%b %d, %Y", // Jan 15, 2024
96            "%d-%b-%Y",  // 15-Jan-2024
97            "%d %B %Y",  // 15 January 2024
98        ];
99
100        for fmt in &formats {
101            if let Ok(date) = NaiveDate::parse_from_str(&date_text, fmt) {
102                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
103                    date_to_serial(&date),
104                )));
105            }
106        }
107
108        Err(ExcelError::new_value()
109            .with_message("DATEVALUE could not parse date text in supported formats"))
110    }
111}
112
113/// Parses a time string and returns its fractional-day serial value.
114///
115/// # Remarks
116/// - Supported formats include 24-hour and AM/PM text forms with optional seconds.
117/// - Result is a fraction in the range `0.0..1.0` and does not include a date component.
118/// - Because only a time fraction is returned, workbook date-system choice does not affect output.
119///
120/// # Examples
121/// ```yaml,sandbox
122/// title: "Parse 24-hour time"
123/// formula: '=TIMEVALUE("14:30")'
124/// expected: 0.6041666667
125/// ```
126///
127/// ```yaml,sandbox
128/// title: "Parse 12-hour AM/PM time"
129/// formula: '=TIMEVALUE("02:30 PM")'
130/// expected: 0.6041666667
131/// ```
132///
133/// ```yaml,docs
134/// related:
135///   - TIME
136///   - DATEVALUE
137///   - SECOND
138/// faq:
139///   - q: "Does TIMEVALUE depend on the 1900 vs 1904 date system?"
140///     a: "No. TIMEVALUE returns only a time fraction, so date-system selection does not change the result."
141/// ```
142#[derive(Debug)]
143pub struct TimeValueFn;
144
145/// [formualizer-docgen:schema:start]
146/// Name: TIMEVALUE
147/// Type: TimeValueFn
148/// Min args: 1
149/// Max args: 1
150/// Variadic: false
151/// Signature: TIMEVALUE(arg1: any@scalar)
152/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
153/// Caps: PURE
154/// [formualizer-docgen:schema:end]
155impl Function for TimeValueFn {
156    func_caps!(PURE);
157
158    fn name(&self) -> &'static str {
159        "TIMEVALUE"
160    }
161
162    fn min_args(&self) -> usize {
163        1
164    }
165
166    fn arg_schema(&self) -> &'static [ArgSchema] {
167        use std::sync::LazyLock;
168        static ONE: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| vec![ArgSchema::any()]);
169        &ONE[..]
170    }
171
172    fn eval<'a, 'b, 'c>(
173        &self,
174        args: &'c [ArgumentHandle<'a, 'b>],
175        _ctx: &dyn FunctionContext<'b>,
176    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
177        let time_text = match args[0].value()?.into_literal() {
178            LiteralValue::Text(s) => s,
179            LiteralValue::Error(e) => {
180                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
181            }
182            other => {
183                return Err(ExcelError::new_value()
184                    .with_message(format!("TIMEVALUE expects text, got {other:?}")));
185            }
186        };
187
188        // Try common time formats
189        let formats = [
190            "%H:%M:%S",    // 14:30:00
191            "%H:%M",       // 14:30
192            "%I:%M:%S %p", // 02:30:00 PM
193            "%I:%M %p",    // 02:30 PM
194        ];
195
196        for fmt in &formats {
197            if let Ok(time) = chrono::NaiveTime::parse_from_str(&time_text, fmt) {
198                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
199                    time_to_fraction(&time),
200                )));
201            }
202        }
203
204        Err(ExcelError::new_value()
205            .with_message("TIMEVALUE could not parse time text in supported formats"))
206    }
207}
208
209pub fn register_builtins() {
210    use std::sync::Arc;
211    crate::function_registry::register_function(Arc::new(DateValueFn));
212    crate::function_registry::register_function(Arc::new(TimeValueFn));
213}
214
215#[cfg(test)]
216mod tests {
217    use super::*;
218    use crate::test_workbook::TestWorkbook;
219    use formualizer_parse::parser::{ASTNode, ASTNodeType};
220    use std::sync::Arc;
221
222    fn lit(v: LiteralValue) -> ASTNode {
223        ASTNode::new(ASTNodeType::Literal(v), None)
224    }
225
226    #[test]
227    fn test_datevalue_formats() {
228        let wb = TestWorkbook::new().with_function(Arc::new(DateValueFn));
229        let ctx = wb.interpreter();
230        let f = ctx.context.get_function("", "DATEVALUE").unwrap();
231
232        // Test ISO format
233        let date_str = lit(LiteralValue::Text("2024-01-15".into()));
234        let result = f
235            .dispatch(
236                &[ArgumentHandle::new(&date_str, &ctx)],
237                &ctx.function_context(None),
238            )
239            .unwrap()
240            .into_literal();
241        assert!(matches!(result, LiteralValue::Number(_)));
242
243        // Test US format
244        let date_str = lit(LiteralValue::Text("01/15/2024".into()));
245        let result = f
246            .dispatch(
247                &[ArgumentHandle::new(&date_str, &ctx)],
248                &ctx.function_context(None),
249            )
250            .unwrap()
251            .into_literal();
252        assert!(matches!(result, LiteralValue::Number(_)));
253    }
254
255    #[test]
256    fn test_timevalue_formats() {
257        let wb = TestWorkbook::new().with_function(Arc::new(TimeValueFn));
258        let ctx = wb.interpreter();
259        let f = ctx.context.get_function("", "TIMEVALUE").unwrap();
260
261        // Test 24-hour format
262        let time_str = lit(LiteralValue::Text("14:30:00".into()));
263        let result = f
264            .dispatch(
265                &[ArgumentHandle::new(&time_str, &ctx)],
266                &ctx.function_context(None),
267            )
268            .unwrap()
269            .into_literal();
270        match result {
271            LiteralValue::Number(n) => {
272                // 14:30 = 14.5/24 ≈ 0.604166...
273                assert!((n - 0.6041666667).abs() < 1e-9);
274            }
275            _ => panic!("TIMEVALUE should return a number"),
276        }
277
278        // Test 12-hour format
279        let time_str = lit(LiteralValue::Text("02:30 PM".into()));
280        let result = f
281            .dispatch(
282                &[ArgumentHandle::new(&time_str, &ctx)],
283                &ctx.function_context(None),
284            )
285            .unwrap()
286            .into_literal();
287        match result {
288            LiteralValue::Number(n) => {
289                assert!((n - 0.6041666667).abs() < 1e-9);
290            }
291            _ => panic!("TIMEVALUE should return a number"),
292        }
293    }
294}
formualizer_eval/builtins/datetime/date_value.rs

formualizer_eval/builtins/datetime/
date_value.rs
