formualizer_eval/builtins/datetime/
edate_eomonth.rs

1//! EDATE and EOMONTH functions for date arithmetic
2
3use super::serial::{date_to_serial, serial_to_date};
4use crate::args::ArgSchema;
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, FunctionContext};
7use chrono::{Datelike, NaiveDate};
8use formualizer_common::{ExcelError, LiteralValue};
9use formualizer_macros::func_caps;
10
11fn coerce_to_serial(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
12    let v = arg.value()?;
13    match v.as_ref() {
14        LiteralValue::Number(f) => Ok(*f),
15        LiteralValue::Int(i) => Ok(*i as f64),
16        LiteralValue::Text(s) => s.parse::<f64>().map_err(|_| {
17            ExcelError::new_value().with_message("EDATE/EOMONTH start_date is not a valid number")
18        }),
19        LiteralValue::Boolean(b) => Ok(if *b { 1.0 } else { 0.0 }),
20        LiteralValue::Empty => Ok(0.0),
21        LiteralValue::Error(e) => Err(e.clone()),
22        _ => Err(ExcelError::new_value()
23            .with_message("EDATE/EOMONTH expects numeric or text-numeric arguments")),
24    }
25}
26
27fn coerce_to_int(arg: &ArgumentHandle) -> Result<i32, ExcelError> {
28    let v = arg.value()?;
29    match v.as_ref() {
30        LiteralValue::Int(i) => Ok(*i as i32),
31        LiteralValue::Number(f) => Ok(f.trunc() as i32),
32        LiteralValue::Text(s) => s.parse::<f64>().map(|f| f.trunc() as i32).map_err(|_| {
33            ExcelError::new_value().with_message("EDATE/EOMONTH months is not a valid number")
34        }),
35        LiteralValue::Boolean(b) => Ok(if *b { 1 } else { 0 }),
36        LiteralValue::Empty => Ok(0),
37        LiteralValue::Error(e) => Err(e.clone()),
38        _ => Err(ExcelError::new_value()
39            .with_message("EDATE/EOMONTH expects numeric or text-numeric arguments")),
40    }
41}
42
43/// EDATE(start_date, months) - Returns date that is months away from start_date
44#[derive(Debug)]
45pub struct EdateFn;
46
47impl Function for EdateFn {
48    func_caps!(PURE);
49
50    fn name(&self) -> &'static str {
51        "EDATE"
52    }
53
54    fn min_args(&self) -> usize {
55        2
56    }
57
58    fn arg_schema(&self) -> &'static [ArgSchema] {
59        use std::sync::LazyLock;
60        static TWO: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
61            vec![
62                // start_date serial (numeric lenient)
63                ArgSchema::number_lenient_scalar(),
64                // months offset (numeric lenient)
65                ArgSchema::number_lenient_scalar(),
66            ]
67        });
68        &TWO[..]
69    }
70
71    fn eval_scalar<'a, 'b>(
72        &self,
73        args: &'a [ArgumentHandle<'a, 'b>],
74        _ctx: &dyn FunctionContext,
75    ) -> Result<LiteralValue, ExcelError> {
76        let start_serial = coerce_to_serial(&args[0])?;
77        let months = coerce_to_int(&args[1])?;
78
79        let start_date = serial_to_date(start_serial)?;
80
81        // Calculate target year and month
82        let total_months = start_date.year() * 12 + start_date.month() as i32 + months;
83        let target_year = total_months / 12;
84        let target_month = ((total_months % 12) + 12) % 12; // Handle negative modulo
85        let target_month = if target_month == 0 { 12 } else { target_month };
86
87        // Keep the same day, but handle month-end overflow
88        let max_day = last_day_of_month(target_year, target_month as u32);
89        let target_day = start_date.day().min(max_day);
90
91        let target_date = NaiveDate::from_ymd_opt(target_year, target_month as u32, target_day)
92            .ok_or_else(ExcelError::new_num)?;
93
94        Ok(LiteralValue::Number(date_to_serial(&target_date)))
95    }
96}
97
98/// EOMONTH(start_date, months) - Returns last day of month that is months away
99#[derive(Debug)]
100pub struct EomonthFn;
101
102impl Function for EomonthFn {
103    func_caps!(PURE);
104
105    fn name(&self) -> &'static str {
106        "EOMONTH"
107    }
108
109    fn min_args(&self) -> usize {
110        2
111    }
112
113    fn arg_schema(&self) -> &'static [ArgSchema] {
114        use std::sync::LazyLock;
115        static TWO: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
116            vec![
117                ArgSchema::number_lenient_scalar(),
118                ArgSchema::number_lenient_scalar(),
119            ]
120        });
121        &TWO[..]
122    }
123
124    fn eval_scalar<'a, 'b>(
125        &self,
126        args: &'a [ArgumentHandle<'a, 'b>],
127        _ctx: &dyn FunctionContext,
128    ) -> Result<LiteralValue, ExcelError> {
129        let start_serial = coerce_to_serial(&args[0])?;
130        let months = coerce_to_int(&args[1])?;
131
132        let start_date = serial_to_date(start_serial)?;
133
134        // Calculate target year and month
135        let total_months = start_date.year() * 12 + start_date.month() as i32 + months;
136        let target_year = total_months / 12;
137        let target_month = ((total_months % 12) + 12) % 12; // Handle negative modulo
138        let target_month = if target_month == 0 { 12 } else { target_month };
139
140        // Get the last day of the target month
141        let last_day = last_day_of_month(target_year, target_month as u32);
142
143        let target_date = NaiveDate::from_ymd_opt(target_year, target_month as u32, last_day)
144            .ok_or_else(ExcelError::new_num)?;
145
146        Ok(LiteralValue::Number(date_to_serial(&target_date)))
147    }
148}
149
150/// Helper to get the last day of a month
151fn last_day_of_month(year: i32, month: u32) -> u32 {
152    // Try day 31, then 30, 29, 28
153    for day in (28..=31).rev() {
154        if NaiveDate::from_ymd_opt(year, month, day).is_some() {
155            return day;
156        }
157    }
158    28 // Fallback (should never reach here for valid months)
159}
160
161pub fn register_builtins() {
162    use std::sync::Arc;
163    crate::function_registry::register_function(Arc::new(EdateFn));
164    crate::function_registry::register_function(Arc::new(EomonthFn));
165}
166
167#[cfg(test)]
168mod tests {
169    use super::*;
170    use crate::test_workbook::TestWorkbook;
171    use formualizer_parse::parser::{ASTNode, ASTNodeType};
172    use std::sync::Arc;
173
174    fn lit(v: LiteralValue) -> ASTNode {
175        ASTNode::new(ASTNodeType::Literal(v), None)
176    }
177
178    #[test]
179    fn test_edate_basic() {
180        let wb = TestWorkbook::new().with_function(Arc::new(EdateFn));
181        let ctx = wb.interpreter();
182        let f = ctx.context.get_function("", "EDATE").unwrap();
183
184        // Test adding months
185        // Use a known date serial (e.g., 44927 = 2023-01-01)
186        let start = lit(LiteralValue::Number(44927.0));
187        let months = lit(LiteralValue::Int(3));
188
189        let result = f
190            .dispatch(
191                &[
192                    ArgumentHandle::new(&start, &ctx),
193                    ArgumentHandle::new(&months, &ctx),
194                ],
195                &ctx.function_context(None),
196            )
197            .unwrap();
198
199        // Should return a date 3 months later
200        assert!(matches!(result, LiteralValue::Number(_)));
201    }
202
203    #[test]
204    fn test_edate_negative_months() {
205        let wb = TestWorkbook::new().with_function(Arc::new(EdateFn));
206        let ctx = wb.interpreter();
207        let f = ctx.context.get_function("", "EDATE").unwrap();
208
209        // Test subtracting months
210        let start = lit(LiteralValue::Number(44927.0)); // 2023-01-01
211        let months = lit(LiteralValue::Int(-2));
212
213        let result = f
214            .dispatch(
215                &[
216                    ArgumentHandle::new(&start, &ctx),
217                    ArgumentHandle::new(&months, &ctx),
218                ],
219                &ctx.function_context(None),
220            )
221            .unwrap();
222
223        // Should return a date 2 months earlier
224        assert!(matches!(result, LiteralValue::Number(_)));
225    }
226
227    #[test]
228    fn test_eomonth_basic() {
229        let wb = TestWorkbook::new().with_function(Arc::new(EomonthFn));
230        let ctx = wb.interpreter();
231        let f = ctx.context.get_function("", "EOMONTH").unwrap();
232
233        // Test end of month
234        let start = lit(LiteralValue::Number(44927.0)); // 2023-01-01
235        let months = lit(LiteralValue::Int(0));
236
237        let result = f
238            .dispatch(
239                &[
240                    ArgumentHandle::new(&start, &ctx),
241                    ArgumentHandle::new(&months, &ctx),
242                ],
243                &ctx.function_context(None),
244            )
245            .unwrap();
246
247        // Should return Jan 31, 2023
248        assert!(matches!(result, LiteralValue::Number(_)));
249    }
250
251    #[test]
252    fn test_eomonth_february() {
253        let wb = TestWorkbook::new().with_function(Arc::new(EomonthFn));
254        let ctx = wb.interpreter();
255        let f = ctx.context.get_function("", "EOMONTH").unwrap();
256
257        // Test February (checking leap year handling)
258        let start = lit(LiteralValue::Number(44927.0)); // 2023-01-01
259        let months = lit(LiteralValue::Int(1)); // Move to February
260
261        let result = f
262            .dispatch(
263                &[
264                    ArgumentHandle::new(&start, &ctx),
265                    ArgumentHandle::new(&months, &ctx),
266                ],
267                &ctx.function_context(None),
268            )
269            .unwrap();
270
271        // Should return Feb 28, 2023 (not a leap year)
272        assert!(matches!(result, LiteralValue::Number(_)));
273    }
274}
formualizer_eval/builtins/datetime/edate_eomonth.rs

formualizer_eval/builtins/datetime/
edate_eomonth.rs
