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

formualizer_eval/builtins/datetime/
edate_eomonth.rs
