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()?.into_literal();
13    if let LiteralValue::Error(e) = v {
14        return Err(e);
15    }
16    crate::coercion::to_number_lenient(&v).map_err(|_| {
17        ExcelError::new_value()
18            .with_message("EDATE/EOMONTH expects numeric, date, or text-numeric arguments")
19    })
20}
21
22fn coerce_to_int(arg: &ArgumentHandle) -> Result<i32, ExcelError> {
23    let v = arg.value()?.into_literal();
24    if let LiteralValue::Error(e) = v {
25        return Err(e);
26    }
27    crate::coercion::to_number_lenient(&v)
28        .map(|f| f.trunc() as i32)
29        .map_err(|_| {
30            ExcelError::new_value()
31                .with_message("EDATE/EOMONTH months argument is not a valid number")
32        })
33}
34
35/// Returns the serial date offset by a whole number of months from a start date.
36///
37/// # Remarks
38/// - `months` is truncated to an integer before calculation.
39/// - If the target month has fewer days, the day is clamped to that month's last valid day.
40/// - Serials are interpreted and emitted with Excel 1900 date mapping (not workbook-specific `1904` mode).
41///
42/// # Examples
43/// ```yaml,sandbox
44/// title: "Add months to first-of-month date"
45/// formula: "=EDATE(44927, 3)"
46/// expected: 45017
47/// ```
48///
49/// ```yaml,sandbox
50/// title: "Clamp month-end overflow"
51/// formula: "=EDATE(45322, 1)"
52/// expected: 45351
53/// ```
54///
55/// ```yaml,docs
56/// related:
57///   - EOMONTH
58///   - DATE
59///   - YEARFRAC
60/// faq:
61///   - q: "What happens when the start day does not exist in the target month?"
62///     a: "EDATE clamps to the last valid day of the target month (for example Jan 31 + 1 month becomes Feb month-end)."
63/// ```
64#[derive(Debug)]
65pub struct EdateFn;
66
67/// [formualizer-docgen:schema:start]
68/// Name: EDATE
69/// Type: EdateFn
70/// Min args: 2
71/// Max args: 2
72/// Variadic: false
73/// Signature: EDATE(arg1: number@scalar, arg2: number@scalar)
74/// 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}
75/// Caps: PURE
76/// [formualizer-docgen:schema:end]
77impl Function for EdateFn {
78    func_caps!(PURE);
79
80    fn name(&self) -> &'static str {
81        "EDATE"
82    }
83
84    fn min_args(&self) -> usize {
85        2
86    }
87
88    fn arg_schema(&self) -> &'static [ArgSchema] {
89        use std::sync::LazyLock;
90        static TWO: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
91            vec![
92                // start_date serial (numeric lenient)
93                ArgSchema::number_lenient_scalar(),
94                // months offset (numeric lenient)
95                ArgSchema::number_lenient_scalar(),
96            ]
97        });
98        &TWO[..]
99    }
100
101    fn eval<'a, 'b, 'c>(
102        &self,
103        args: &'c [ArgumentHandle<'a, 'b>],
104        _ctx: &dyn FunctionContext<'b>,
105    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
106        let start_serial = coerce_to_serial(&args[0])?;
107        let months = coerce_to_int(&args[1])?;
108
109        let start_date = serial_to_date(start_serial)?;
110
111        // Calculate target year and month using Euclidean division
112        let total_months =
113            start_date.year() as i64 * 12 + start_date.month() as i64 + months as i64;
114        let tm = total_months - 1;
115        let target_year = tm.div_euclid(12) as i32;
116        let target_month = (tm.rem_euclid(12) + 1) as u32;
117
118        // Keep the same day, but handle month-end overflow
119        let max_day = last_day_of_month(target_year, target_month);
120        let target_day = start_date.day().min(max_day);
121
122        let target_date = NaiveDate::from_ymd_opt(target_year, target_month, target_day)
123            .ok_or_else(ExcelError::new_num)?;
124
125        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
126            date_to_serial(&target_date),
127        )))
128    }
129}
130
131/// Returns the serial for the last day of the month at a month offset from a start date.
132///
133/// # Remarks
134/// - `months` is truncated to an integer before offset calculation.
135/// - The returned date is always the month-end date for the target month.
136/// - Serials are interpreted and returned using Excel 1900 mapping rather than workbook `1904` mode.
137///
138/// # Examples
139/// ```yaml,sandbox
140/// title: "Get end of current month"
141/// formula: "=EOMONTH(44927, 0)"
142/// expected: 44957
143/// ```
144///
145/// ```yaml,sandbox
146/// title: "Get end of month two months ahead"
147/// formula: "=EOMONTH(45322, 2)"
148/// expected: 45382
149/// ```
150///
151/// ```yaml,docs
152/// related:
153///   - EDATE
154///   - DATE
155///   - DAY
156/// faq:
157///   - q: "Does EOMONTH always return a month-end date?"
158///     a: "Yes. Regardless of the start day, EOMONTH returns the final calendar day of the target month after offset."
159/// ```
160#[derive(Debug)]
161pub struct EomonthFn;
162
163/// [formualizer-docgen:schema:start]
164/// Name: EOMONTH
165/// Type: EomonthFn
166/// Min args: 2
167/// Max args: 2
168/// Variadic: false
169/// Signature: EOMONTH(arg1: number@scalar, arg2: number@scalar)
170/// 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}
171/// Caps: PURE
172/// [formualizer-docgen:schema:end]
173impl Function for EomonthFn {
174    func_caps!(PURE);
175
176    fn name(&self) -> &'static str {
177        "EOMONTH"
178    }
179
180    fn min_args(&self) -> usize {
181        2
182    }
183
184    fn arg_schema(&self) -> &'static [ArgSchema] {
185        use std::sync::LazyLock;
186        static TWO: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
187            vec![
188                ArgSchema::number_lenient_scalar(),
189                ArgSchema::number_lenient_scalar(),
190            ]
191        });
192        &TWO[..]
193    }
194
195    fn eval<'a, 'b, 'c>(
196        &self,
197        args: &'c [ArgumentHandle<'a, 'b>],
198        _ctx: &dyn FunctionContext<'b>,
199    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
200        let start_serial = coerce_to_serial(&args[0])?;
201        let months = coerce_to_int(&args[1])?;
202
203        let start_date = serial_to_date(start_serial)?;
204
205        // Calculate target year and month using Euclidean division
206        let total_months =
207            start_date.year() as i64 * 12 + start_date.month() as i64 + months as i64;
208        let tm = total_months - 1;
209        let target_year = tm.div_euclid(12) as i32;
210        let target_month = (tm.rem_euclid(12) + 1) as u32;
211
212        // Get the last day of the target month
213        let last_day = last_day_of_month(target_year, target_month);
214
215        let target_date = NaiveDate::from_ymd_opt(target_year, target_month, last_day)
216            .ok_or_else(ExcelError::new_num)?;
217
218        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
219            date_to_serial(&target_date),
220        )))
221    }
222}
223
224/// Helper to get the last day of a month
225fn last_day_of_month(year: i32, month: u32) -> u32 {
226    // Try day 31, then 30, 29, 28
227    for day in (28..=31).rev() {
228        if NaiveDate::from_ymd_opt(year, month, day).is_some() {
229            return day;
230        }
231    }
232    28 // Fallback (should never reach here for valid months)
233}
234
235pub fn register_builtins() {
236    use std::sync::Arc;
237    crate::function_registry::register_function(Arc::new(EdateFn));
238    crate::function_registry::register_function(Arc::new(EomonthFn));
239}
240
241#[cfg(test)]
242mod tests {
243    use super::*;
244    use crate::test_workbook::TestWorkbook;
245    use formualizer_parse::parser::{ASTNode, ASTNodeType};
246    use std::sync::Arc;
247
248    fn lit(v: LiteralValue) -> ASTNode {
249        ASTNode::new(ASTNodeType::Literal(v), None)
250    }
251
252    #[test]
253    fn test_edate_basic() {
254        let wb = TestWorkbook::new().with_function(Arc::new(EdateFn));
255        let ctx = wb.interpreter();
256        let f = ctx.context.get_function("", "EDATE").unwrap();
257
258        // Test adding months
259        // Use a known date serial (e.g., 44927 = 2023-01-01)
260        let start = lit(LiteralValue::Number(44927.0));
261        let months = lit(LiteralValue::Int(3));
262
263        let result = f
264            .dispatch(
265                &[
266                    ArgumentHandle::new(&start, &ctx),
267                    ArgumentHandle::new(&months, &ctx),
268                ],
269                &ctx.function_context(None),
270            )
271            .unwrap()
272            .into_literal();
273
274        // Should return a date 3 months later
275        assert!(matches!(result, LiteralValue::Number(_)));
276    }
277
278    #[test]
279    fn test_edate_negative_months() {
280        let wb = TestWorkbook::new().with_function(Arc::new(EdateFn));
281        let ctx = wb.interpreter();
282        let f = ctx.context.get_function("", "EDATE").unwrap();
283
284        // Test subtracting months
285        let start = lit(LiteralValue::Number(44927.0)); // 2023-01-01
286        let months = lit(LiteralValue::Int(-2));
287
288        let result = f
289            .dispatch(
290                &[
291                    ArgumentHandle::new(&start, &ctx),
292                    ArgumentHandle::new(&months, &ctx),
293                ],
294                &ctx.function_context(None),
295            )
296            .unwrap()
297            .into_literal();
298
299        // Should return a date 2 months earlier
300        assert!(matches!(result, LiteralValue::Number(_)));
301    }
302
303    #[test]
304    fn test_eomonth_basic() {
305        let wb = TestWorkbook::new().with_function(Arc::new(EomonthFn));
306        let ctx = wb.interpreter();
307        let f = ctx.context.get_function("", "EOMONTH").unwrap();
308
309        // Test end of month
310        let start = lit(LiteralValue::Number(44927.0)); // 2023-01-01
311        let months = lit(LiteralValue::Int(0));
312
313        let result = f
314            .dispatch(
315                &[
316                    ArgumentHandle::new(&start, &ctx),
317                    ArgumentHandle::new(&months, &ctx),
318                ],
319                &ctx.function_context(None),
320            )
321            .unwrap()
322            .into_literal();
323
324        // Should return Jan 31, 2023
325        assert!(matches!(result, LiteralValue::Number(_)));
326    }
327
328    #[test]
329    fn test_eomonth_february() {
330        let wb = TestWorkbook::new().with_function(Arc::new(EomonthFn));
331        let ctx = wb.interpreter();
332        let f = ctx.context.get_function("", "EOMONTH").unwrap();
333
334        // Test February (checking leap year handling)
335        let start = lit(LiteralValue::Number(44927.0)); // 2023-01-01
336        let months = lit(LiteralValue::Int(1)); // Move to February
337
338        let result = f
339            .dispatch(
340                &[
341                    ArgumentHandle::new(&start, &ctx),
342                    ArgumentHandle::new(&months, &ctx),
343                ],
344                &ctx.function_context(None),
345            )
346            .unwrap()
347            .into_literal();
348
349        // Should return Feb 28, 2023 (not a leap year)
350        assert!(matches!(result, LiteralValue::Number(_)));
351    }
352}
formualizer_eval/builtins/datetime/edate_eomonth.rs

formualizer_eval/builtins/datetime/
edate_eomonth.rs
