formualizer_eval/builtins/datetime/
weekday_workday.rs

1//! WEEKDAY, WEEKNUM, DATEDIF, NETWORKDAYS, WORKDAY functions
2
3use super::serial::{date_to_serial, serial_to_date};
4use crate::args::ArgSchema;
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
7use chrono::{Datelike, NaiveDate, Weekday};
8use formualizer_common::{ExcelError, LiteralValue};
9use formualizer_macros::func_caps;
10
11/// Day of year in a standard 365-day (non-leap) year.
12/// Feb 29 dates are clamped to Feb 28 (day 59).
13fn non_leap_day_of_year(month: u32, day: u32) -> i64 {
14    const CUM: [i64; 12] = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334];
15    const DAYS: [u32; 12] = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31];
16    let capped = day.min(DAYS[(month - 1) as usize]);
17    CUM[(month - 1) as usize] + capped as i64
18}
19
20fn coerce_to_serial(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
21    let v = arg.value()?.into_literal();
22    match v {
23        LiteralValue::Number(f) => Ok(f),
24        LiteralValue::Int(i) => Ok(i as f64),
25        LiteralValue::Date(d) => Ok(date_to_serial(&d)),
26        LiteralValue::DateTime(dt) => Ok(date_to_serial(&dt.date())),
27        LiteralValue::Text(s) => s
28            .parse::<f64>()
29            .map_err(|_| ExcelError::new_value().with_message("Not a valid number")),
30        LiteralValue::Boolean(b) => Ok(if b { 1.0 } else { 0.0 }),
31        LiteralValue::Empty => Ok(0.0),
32        LiteralValue::Error(e) => Err(e),
33        _ => Err(ExcelError::new_value()),
34    }
35}
36
37fn coerce_to_int(arg: &ArgumentHandle) -> Result<i64, ExcelError> {
38    let v = arg.value()?.into_literal();
39    match v {
40        LiteralValue::Number(f) => Ok(f.trunc() as i64),
41        LiteralValue::Int(i) => Ok(i),
42        LiteralValue::Boolean(b) => Ok(if b { 1 } else { 0 }),
43        LiteralValue::Empty => Ok(0),
44        LiteralValue::Error(e) => Err(e),
45        _ => Err(ExcelError::new_value()),
46    }
47}
48
49/// Returns the day-of-week index for a date serial with configurable numbering.
50///
51/// # Remarks
52/// - Default `return_type` is `1` (`Sunday=1` through `Saturday=7`).
53/// - Supported `return_type` values are `1`, `2`, `3`, `11`-`17`; unsupported values return `#NUM!`.
54/// - Input serials are interpreted with Excel 1900 date mapping, including its historical leap-year quirk.
55///
56/// # Examples
57/// ```yaml,sandbox
58/// title: "Default numbering (Sunday-first)"
59/// formula: "=WEEKDAY(45292)"
60/// expected: 2
61/// ```
62///
63/// ```yaml,sandbox
64/// title: "Monday-first numbering"
65/// formula: "=WEEKDAY(45292, 2)"
66/// expected: 1
67/// ```
68///
69/// ```yaml,docs
70/// related:
71///   - WEEKNUM
72///   - ISOWEEKNUM
73///   - WORKDAY
74/// faq:
75///   - q: "Why do I get #NUM! for some return_type values?"
76///     a: "WEEKDAY only accepts specific Excel return_type codes (1, 2, 3, 11-17); other codes return #NUM!."
77/// ```
78#[derive(Debug)]
79pub struct WeekdayFn;
80/// [formualizer-docgen:schema:start]
81/// Name: WEEKDAY
82/// Type: WeekdayFn
83/// Min args: 1
84/// Max args: variadic
85/// Variadic: true
86/// Signature: WEEKDAY(arg1: number@scalar, arg2...: number@scalar)
87/// 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}
88/// Caps: PURE
89/// [formualizer-docgen:schema:end]
90impl Function for WeekdayFn {
91    func_caps!(PURE);
92    fn name(&self) -> &'static str {
93        "WEEKDAY"
94    }
95    fn min_args(&self) -> usize {
96        1
97    }
98    fn variadic(&self) -> bool {
99        true
100    }
101    fn arg_schema(&self) -> &'static [ArgSchema] {
102        use std::sync::LazyLock;
103        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
104            vec![
105                ArgSchema::number_lenient_scalar(),
106                ArgSchema::number_lenient_scalar(),
107            ]
108        });
109        &SCHEMA[..]
110    }
111    fn eval<'a, 'b, 'c>(
112        &self,
113        args: &'c [ArgumentHandle<'a, 'b>],
114        _ctx: &dyn FunctionContext<'b>,
115    ) -> Result<CalcValue<'b>, ExcelError> {
116        let serial = coerce_to_serial(&args[0])?;
117        let serial_int = serial.trunc() as i64;
118        if serial_int < 0 {
119            return Ok(CalcValue::Scalar(
120                LiteralValue::Error(ExcelError::new_num()),
121            ));
122        }
123        let return_type = if args.len() > 1 {
124            coerce_to_int(&args[1])?
125        } else {
126            1
127        };
128
129        // Compute weekday directly from serial number (not chrono) to correctly
130        // handle Excel's phantom Feb 29. serial % 7: 0=Sat, 1=Sun, 2=Mon, ..., 6=Fri
131        let d = serial_int % 7;
132
133        // Map return_type to the d-value of its starting day and whether 0-based
134        let (start_d, zero_based) = match return_type {
135            1 | 17 => (1i64, false), // Sun=1..Sat=7
136            2 | 11 => (2, false),    // Mon=1..Sun=7
137            3 => (2, true),          // Mon=0..Sun=6
138            12 => (3, false),        // Tue=1..Mon=7
139            13 => (4, false),        // Wed=1..Tue=7
140            14 => (5, false),        // Thu=1..Wed=7
141            15 => (6, false),        // Fri=1..Thu=7
142            16 => (0, false),        // Sat=1..Fri=7
143            _ => {
144                return Ok(CalcValue::Scalar(
145                    LiteralValue::Error(ExcelError::new_num()),
146                ));
147            }
148        };
149
150        let result = if zero_based {
151            (d - start_d + 7) % 7
152        } else {
153            (d - start_d + 7) % 7 + 1
154        };
155
156        Ok(CalcValue::Scalar(LiteralValue::Int(result)))
157    }
158}
159
160/// Returns the week number of the year for a date serial.
161///
162/// # Remarks
163/// - Default `return_type` is `1` (week starts on Sunday).
164/// - Supported `return_type` values are `1`, `2`, `11`-`17`, and `21` (ISO week numbering).
165/// - Unsupported `return_type` values return `#NUM!`.
166/// - Input serials are interpreted using Excel 1900 date mapping rather than workbook `1904` interpretation.
167///
168/// # Examples
169/// ```yaml,sandbox
170/// title: "Default week numbering"
171/// formula: "=WEEKNUM(45292)"
172/// expected: 1
173/// ```
174///
175/// ```yaml,sandbox
176/// title: "ISO week numbering"
177/// formula: "=WEEKNUM(42370, 21)"
178/// expected: 53
179/// ```
180///
181/// ```yaml,docs
182/// related:
183///   - WEEKDAY
184///   - ISOWEEKNUM
185///   - DATE
186/// faq:
187///   - q: "What is special about return_type 21 in WEEKNUM?"
188///     a: "return_type=21 switches to ISO week numbering, matching ISOWEEKNUM behavior."
189/// ```
190#[derive(Debug)]
191pub struct WeeknumFn;
192/// [formualizer-docgen:schema:start]
193/// Name: WEEKNUM
194/// Type: WeeknumFn
195/// Min args: 1
196/// Max args: variadic
197/// Variadic: true
198/// Signature: WEEKNUM(arg1: number@scalar, arg2...: number@scalar)
199/// 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}
200/// Caps: PURE
201/// [formualizer-docgen:schema:end]
202impl Function for WeeknumFn {
203    func_caps!(PURE);
204    fn name(&self) -> &'static str {
205        "WEEKNUM"
206    }
207    fn min_args(&self) -> usize {
208        1
209    }
210    fn variadic(&self) -> bool {
211        true
212    }
213    fn arg_schema(&self) -> &'static [ArgSchema] {
214        use std::sync::LazyLock;
215        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
216            vec![
217                ArgSchema::number_lenient_scalar(),
218                ArgSchema::number_lenient_scalar(),
219            ]
220        });
221        &SCHEMA[..]
222    }
223    fn eval<'a, 'b, 'c>(
224        &self,
225        args: &'c [ArgumentHandle<'a, 'b>],
226        _ctx: &dyn FunctionContext<'b>,
227    ) -> Result<CalcValue<'b>, ExcelError> {
228        let serial = coerce_to_serial(&args[0])?;
229        let serial_int = serial.trunc() as i64;
230        if serial_int < 0 {
231            return Ok(CalcValue::Scalar(
232                LiteralValue::Error(ExcelError::new_num()),
233            ));
234        }
235        let return_type = if args.len() > 1 {
236            coerce_to_int(&args[1])?
237        } else {
238            1
239        };
240
241        // Serial 0 ("January 0, 1900") is before the first week of any year
242        if serial_int == 0 {
243            return Ok(CalcValue::Scalar(LiteralValue::Int(0)));
244        }
245
246        if return_type == 21 {
247            // ISO week number: computed from serial-based weekday
248            // serial % 7: 0=Sat, 1=Sun, 2=Mon, ..., 6=Fri
249            let d = serial_int % 7;
250            // ISO weekday: Mon=1, ..., Sun=7
251            let iso_wd = if d < 2 { d + 6 } else { d - 1 };
252
253            // Thursday of this ISO week
254            let thu_serial = serial_int - iso_wd + 4;
255
256            if thu_serial < 1 {
257                // Falls in last week of previous year (only for first days of 1900)
258                return Ok(CalcValue::Scalar(LiteralValue::Int(52)));
259            }
260
261            // Get year of the Thursday
262            let thu_date = serial_to_date(thu_serial as f64)?;
263            let thu_year = thu_date.year();
264
265            // Serial for Jan 1 of that year
266            let jan1 = NaiveDate::from_ymd_opt(thu_year, 1, 1).unwrap();
267            let jan1_serial = date_to_serial(&jan1) as i64;
268
269            let week = (thu_serial - jan1_serial) / 7 + 1;
270            return Ok(CalcValue::Scalar(LiteralValue::Int(week)));
271        }
272
273        // Non-ISO week number using serial-based weekday for Jan 1
274        // Starting weekday as d-value: 0=Sat, 1=Sun, 2=Mon, ..., 6=Fri
275        let week_starts_d: i64 = match return_type {
276            1 | 17 => 1, // Sunday
277            2 | 11 => 2, // Monday
278            12 => 3,     // Tuesday
279            13 => 4,     // Wednesday
280            14 => 5,     // Thursday
281            15 => 6,     // Friday
282            16 => 0,     // Saturday
283            _ => {
284                return Ok(CalcValue::Scalar(
285                    LiteralValue::Error(ExcelError::new_num()),
286                ));
287            }
288        };
289
290        // Get the year for this serial
291        let date = serial_to_date(serial)?;
292        let year = date.year();
293
294        // Serial for Jan 1 of the year
295        let jan1 = NaiveDate::from_ymd_opt(year, 1, 1).unwrap();
296        let jan1_serial = date_to_serial(&jan1) as i64;
297
298        // Jan 1's weekday (d-value from serial)
299        let jan1_d = jan1_serial % 7;
300
301        // Offset: how many days from week_starts to Jan 1
302        let offset = (jan1_d - week_starts_d + 7) % 7;
303
304        // Day of year (1-based)
305        let day_of_year = serial_int - jan1_serial + 1;
306
307        // Week number: Jan 1 is always in week 1
308        let week = (day_of_year + offset - 1) / 7 + 1;
309
310        Ok(CalcValue::Scalar(LiteralValue::Int(week)))
311    }
312}
313
314/// Returns the difference between two dates in a requested unit.
315///
316/// # Remarks
317/// - Supported units are `"Y"`, `"M"`, `"D"`, `"MD"`, `"YM"`, and `"YD"`.
318/// - If `start_date > end_date`, the function returns `#NUM!`.
319/// - Unit matching is case-insensitive.
320/// - `"YD"` uses a Feb-29 normalization strategy that can differ slightly from Excel in edge cases.
321/// - Input serials are interpreted with Excel 1900 date mapping.
322///
323/// # Examples
324/// ```yaml,sandbox
325/// title: "Difference in days"
326/// formula: '=DATEDIF(44197, 44378, "D")'
327/// expected: 181
328/// ```
329///
330/// ```yaml,sandbox
331/// title: "Complete months difference"
332/// formula: '=DATEDIF(44197, 44378, "M")'
333/// expected: 6
334/// ```
335///
336/// ```yaml,docs
337/// related:
338///   - DAYS
339///   - YEARFRAC
340///   - DATE
341/// faq:
342///   - q: "How are unit strings interpreted in DATEDIF?"
343///     a: "Unit text is case-insensitive, but only Y, M, D, MD, YM, and YD are supported; other units return #NUM!."
344/// ```
345#[derive(Debug)]
346pub struct DatedifFn;
347/// [formualizer-docgen:schema:start]
348/// Name: DATEDIF
349/// Type: DatedifFn
350/// Min args: 3
351/// Max args: 3
352/// Variadic: false
353/// Signature: DATEDIF(arg1: number@scalar, arg2: number@scalar, arg3: any@scalar)
354/// 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}; arg3{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
355/// Caps: PURE
356/// [formualizer-docgen:schema:end]
357impl Function for DatedifFn {
358    func_caps!(PURE);
359    fn name(&self) -> &'static str {
360        "DATEDIF"
361    }
362    fn min_args(&self) -> usize {
363        3
364    }
365    fn arg_schema(&self) -> &'static [ArgSchema] {
366        use std::sync::LazyLock;
367        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
368            vec![
369                ArgSchema::number_lenient_scalar(),
370                ArgSchema::number_lenient_scalar(),
371                ArgSchema::any(),
372            ]
373        });
374        &SCHEMA[..]
375    }
376    fn eval<'a, 'b, 'c>(
377        &self,
378        args: &'c [ArgumentHandle<'a, 'b>],
379        _ctx: &dyn FunctionContext<'b>,
380    ) -> Result<CalcValue<'b>, ExcelError> {
381        let start_serial = coerce_to_serial(&args[0])?;
382        let end_serial = coerce_to_serial(&args[1])?;
383
384        let unit = match args[2].value()?.into_literal() {
385            LiteralValue::Text(s) => s.to_uppercase(),
386            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
387            _ => {
388                return Ok(CalcValue::Scalar(LiteralValue::Error(
389                    ExcelError::new_value(),
390                )));
391            }
392        };
393
394        if start_serial > end_serial {
395            return Ok(CalcValue::Scalar(
396                LiteralValue::Error(ExcelError::new_num()),
397            ));
398        }
399
400        let start_date = serial_to_date(start_serial)?;
401        let end_date = serial_to_date(end_serial)?;
402
403        let result = match unit.as_str() {
404            "Y" => {
405                // Complete years
406                let mut years = end_date.year() - start_date.year();
407                if (end_date.month(), end_date.day()) < (start_date.month(), start_date.day()) {
408                    years -= 1;
409                }
410                years as i64
411            }
412            "M" => {
413                // Complete months
414                let mut months = (end_date.year() - start_date.year()) * 12
415                    + (end_date.month() as i32 - start_date.month() as i32);
416                if end_date.day() < start_date.day() {
417                    months -= 1;
418                }
419                months as i64
420            }
421            "D" => {
422                // Days
423                (end_date - start_date).num_days()
424            }
425            "MD" => {
426                // Days ignoring months and years
427                let mut days = end_date.day() as i64 - start_date.day() as i64;
428                if days < 0 {
429                    // Get days in the previous month
430                    let prev_month = if end_date.month() == 1 {
431                        NaiveDate::from_ymd_opt(end_date.year() - 1, 12, 1)
432                    } else {
433                        NaiveDate::from_ymd_opt(end_date.year(), end_date.month() - 1, 1)
434                    }
435                    .unwrap();
436                    let days_in_prev_month = (NaiveDate::from_ymd_opt(
437                        if prev_month.month() == 12 {
438                            prev_month.year() + 1
439                        } else {
440                            prev_month.year()
441                        },
442                        if prev_month.month() == 12 {
443                            1
444                        } else {
445                            prev_month.month() + 1
446                        },
447                        1,
448                    )
449                    .unwrap()
450                        - prev_month)
451                        .num_days();
452                    days += days_in_prev_month;
453                }
454                days
455            }
456            "YM" => {
457                // Months ignoring years
458                let mut months = end_date.month() as i64 - start_date.month() as i64;
459                if end_date.day() < start_date.day() {
460                    months -= 1;
461                }
462                if months < 0 {
463                    months += 12;
464                }
465                months
466            }
467            "YD" => {
468                // Days ignoring years: use day-of-year in a non-leap context
469                // to match Excel/LibreOffice behavior (consistent 365-day year)
470                let start_doy = non_leap_day_of_year(start_date.month(), start_date.day());
471                let end_doy = non_leap_day_of_year(end_date.month(), end_date.day());
472                if end_doy >= start_doy {
473                    end_doy - start_doy
474                } else {
475                    365 - start_doy + end_doy
476                }
477            }
478            _ => {
479                return Ok(CalcValue::Scalar(
480                    LiteralValue::Error(ExcelError::new_num()),
481                ));
482            }
483        };
484
485        Ok(CalcValue::Scalar(LiteralValue::Int(result)))
486    }
487}
488
489/// Helper: check if a date is a weekend (Saturday or Sunday)
490fn is_weekend(date: &NaiveDate) -> bool {
491    matches!(date.weekday(), Weekday::Sat | Weekday::Sun)
492}
493
494/// Weekend mask: 7 bools indexed by chrono weekday (Mon=0 .. Sun=6).
495/// `true` means the day is a non-working (weekend) day.
496type WeekendMask = [bool; 7];
497
498/// Default mask: Saturday and Sunday are weekends.
499const DEFAULT_WEEKEND_MASK: WeekendMask = [false, false, false, false, false, true, true];
500
501/// Parse the `weekend` argument for `.INTL` functions.
502///
503/// Accepts either:
504/// - A **number** code (1-7 for two-day weekends, 11-17 for single-day weekends).
505/// - A **7-character string** of `0`s and `1`s (`1` = weekend) starting from Monday.
506///
507/// Returns `None` if the value is invalid (caller should return `#NUM!`).
508/// The special all-ones string (`"1111111"`) yields `#VALUE!` per Excel.
509fn parse_weekend_mask(arg: &ArgumentHandle) -> Result<Option<WeekendMask>, ExcelError> {
510    let v = arg.value()?.into_literal();
511    match v {
512        LiteralValue::Number(f) => Ok(weekend_mask_from_code(f.trunc() as i64)),
513        LiteralValue::Int(i) => Ok(weekend_mask_from_code(i)),
514        LiteralValue::Boolean(b) => {
515            // TRUE = 1, FALSE not valid (0 weekend days isn't a valid code)
516            if b {
517                Ok(weekend_mask_from_code(1))
518            } else {
519                Ok(None)
520            }
521        }
522        LiteralValue::Text(s) => {
523            if s == "1111111" {
524                Err(ExcelError::new_value())
525            } else {
526                Ok(weekend_mask_from_string(&s))
527            }
528        }
529        LiteralValue::Empty => Ok(Some(DEFAULT_WEEKEND_MASK)),
530        LiteralValue::Error(e) => Err(e),
531        _ => Ok(None),
532    }
533}
534
535/// Map a numeric weekend code to a 7-element mask.
536fn weekend_mask_from_code(code: i64) -> Option<WeekendMask> {
537    // Indices: 0=Mon, 1=Tue, 2=Wed, 3=Thu, 4=Fri, 5=Sat, 6=Sun
538    match code {
539        1 => Some([false, false, false, false, false, true, true]), // Sat, Sun
540        2 => Some([true, false, false, false, false, false, true]), // Sun, Mon
541        3 => Some([true, true, false, false, false, false, false]), // Mon, Tue
542        4 => Some([false, true, true, false, false, false, false]), // Tue, Wed
543        5 => Some([false, false, true, true, false, false, false]), // Wed, Thu
544        6 => Some([false, false, false, true, true, false, false]), // Thu, Fri
545        7 => Some([false, false, false, false, true, true, false]), // Fri, Sat
546        11 => Some([false, false, false, false, false, false, true]), // Sun only
547        12 => Some([true, false, false, false, false, false, false]), // Mon only
548        13 => Some([false, true, false, false, false, false, false]), // Tue only
549        14 => Some([false, false, true, false, false, false, false]), // Wed only
550        15 => Some([false, false, false, true, false, false, false]), // Thu only
551        16 => Some([false, false, false, false, true, false, false]), // Fri only
552        17 => Some([false, false, false, false, false, true, false]), // Sat only
553        _ => None,
554    }
555}
556
557/// Parse a 7-character "0"/"1" string into a weekend mask.
558/// Characters map to Mon..Sun. "1" = weekend, "0" = workday.
559/// Returns `None` if the string is invalid or all-ones (no workdays).
560fn weekend_mask_from_string(s: &str) -> Option<WeekendMask> {
561    if s.len() != 7 {
562        return None;
563    }
564    let mut mask = [false; 7];
565    let mut all_weekend = true;
566    for (i, ch) in s.chars().enumerate() {
567        match ch {
568            '1' => mask[i] = true,
569            '0' => {
570                mask[i] = false;
571                all_weekend = false;
572            }
573            _ => return None,
574        }
575    }
576    if all_weekend {
577        return None; // Excel returns #VALUE! when all 7 days are weekends
578    }
579    Some(mask)
580}
581
582/// Check whether a date falls on a weekend day according to the given mask.
583fn is_weekend_masked(date: &NaiveDate, mask: &WeekendMask) -> bool {
584    let idx = date.weekday().num_days_from_monday() as usize; // Mon=0..Sun=6
585    mask[idx]
586}
587
588/// Collect holiday dates from argument(s) starting at `arg_start`.
589/// Handles scalars, inline arrays, and range references.
590/// Silently skips non-numeric / empty cells (matching Excel behavior).
591fn collect_holidays(args: &[ArgumentHandle], arg_start: usize) -> Vec<NaiveDate> {
592    let mut holidays = Vec::new();
593    for arg in args.iter().skip(arg_start) {
594        match arg.value() {
595            Ok(CalcValue::Scalar(lit)) => collect_holidays_from_literal(&lit, &mut holidays),
596            Ok(CalcValue::Range(rv)) => {
597                let _ = rv.for_each_cell(&mut |val| {
598                    collect_holidays_from_literal(val, &mut holidays);
599                    Ok(())
600                });
601            }
602            _ => {}
603        }
604    }
605    holidays
606}
607
608fn collect_holidays_from_literal(lit: &LiteralValue, out: &mut Vec<NaiveDate>) {
609    match lit {
610        LiteralValue::Array(rows) => {
611            for row in rows {
612                for cell in row {
613                    collect_holidays_from_literal(cell, out);
614                }
615            }
616        }
617        _ => {
618            if let Some(d) = literal_to_date(lit) {
619                out.push(d);
620            }
621        }
622    }
623}
624
625fn literal_to_date(lit: &LiteralValue) -> Option<NaiveDate> {
626    match lit {
627        LiteralValue::Number(f) => serial_to_date(*f).ok(),
628        LiteralValue::Int(i) => serial_to_date(*i as f64).ok(),
629        LiteralValue::Date(d) => Some(*d),
630        LiteralValue::DateTime(dt) => Some(dt.date()),
631        _ => None,
632    }
633}
634
635/// Returns the number of weekday business days between two dates, inclusive.
636///
637/// # Remarks
638/// - Weekends are fixed to Saturday and Sunday.
639/// - If `start_date > end_date`, the result is negative.
640/// - Optional `holidays` arguments are excluded from the count and may be provided as scalars,
641///   inline arrays, or ranges.
642/// - Input serials are interpreted with Excel 1900 date mapping.
643///
644/// # Examples
645/// ```yaml,sandbox
646/// title: "Count weekdays in a range"
647/// formula: "=NETWORKDAYS(45292, 45299)"
648/// expected: 6
649/// ```
650///
651/// ```yaml,sandbox
652/// title: "Holiday exclusions reduce the count"
653/// formula: "=NETWORKDAYS(45292, 45299, 45293)"
654/// expected: 5
655/// ```
656///
657/// ```yaml,docs
658/// related:
659///   - WORKDAY
660///   - WEEKDAY
661///   - DAYS
662/// faq:
663///   - q: "Can I exclude custom holidays in NETWORKDAYS?"
664///     a: "Yes. Additional arguments can provide holiday serials, arrays, or ranges to exclude from the weekday count."
665/// ```
666#[derive(Debug)]
667pub struct NetworkdaysFn;
668/// [formualizer-docgen:schema:start]
669/// Name: NETWORKDAYS
670/// Type: NetworkdaysFn
671/// Min args: 2
672/// Max args: variadic
673/// Variadic: true
674/// Signature: NETWORKDAYS(arg1: number@scalar, arg2: number@scalar, arg3...: any@scalar)
675/// 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}; arg3{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
676/// Caps: PURE
677/// [formualizer-docgen:schema:end]
678impl Function for NetworkdaysFn {
679    func_caps!(PURE);
680    fn name(&self) -> &'static str {
681        "NETWORKDAYS"
682    }
683    fn min_args(&self) -> usize {
684        2
685    }
686    fn variadic(&self) -> bool {
687        true
688    }
689    fn arg_schema(&self) -> &'static [ArgSchema] {
690        use std::sync::LazyLock;
691        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
692            vec![
693                ArgSchema::number_lenient_scalar(),
694                ArgSchema::number_lenient_scalar(),
695                ArgSchema::any(), // holidays (optional)
696            ]
697        });
698        &SCHEMA[..]
699    }
700    fn eval<'a, 'b, 'c>(
701        &self,
702        args: &'c [ArgumentHandle<'a, 'b>],
703        _ctx: &dyn FunctionContext<'b>,
704    ) -> Result<CalcValue<'b>, ExcelError> {
705        let start_serial = coerce_to_serial(&args[0])?;
706        let end_serial = coerce_to_serial(&args[1])?;
707
708        let start_date = serial_to_date(start_serial)?;
709        let end_date = serial_to_date(end_serial)?;
710
711        let holidays = collect_holidays(args, 2);
712
713        let (start, end, sign) = if start_date <= end_date {
714            (start_date, end_date, 1i64)
715        } else {
716            (end_date, start_date, -1i64)
717        };
718
719        let mut count = 0i64;
720        let mut current = start;
721        while current <= end {
722            if !is_weekend(&current) && !holidays.contains(&current) {
723                count += 1;
724            }
725            current = current.succ_opt().unwrap_or(current);
726        }
727
728        Ok(CalcValue::Scalar(LiteralValue::Int(count * sign)))
729    }
730}
731
732/// Returns the date serial that is a given number of weekdays from a start date.
733///
734/// # Remarks
735/// - Positive `days` moves forward; negative `days` moves backward.
736/// - Weekends are fixed to Saturday and Sunday.
737/// - Optional `holidays` arguments are excluded and may be provided as scalars, inline arrays,
738///   or ranges.
739/// - Input and output serials use Excel 1900 date mapping.
740///
741/// # Examples
742/// ```yaml,sandbox
743/// title: "Move forward by five workdays"
744/// formula: "=WORKDAY(45292, 5)"
745/// expected: 45299
746/// ```
747///
748/// ```yaml,sandbox
749/// title: "Holiday exclusions push the target date out"
750/// formula: "=WORKDAY(45292, 5, 45293)"
751/// expected: 45300
752/// ```
753///
754/// ```yaml,docs
755/// related:
756///   - NETWORKDAYS
757///   - WEEKDAY
758///   - TODAY
759/// faq:
760///   - q: "Does WORKDAY include the start date when days=0?"
761///     a: "Yes. With zero offset, WORKDAY returns the start date serial unchanged; nonzero offsets skip weekend days while stepping."
762/// ```
763#[derive(Debug)]
764pub struct WorkdayFn;
765/// [formualizer-docgen:schema:start]
766/// Name: WORKDAY
767/// Type: WorkdayFn
768/// Min args: 2
769/// Max args: variadic
770/// Variadic: true
771/// Signature: WORKDAY(arg1: number@scalar, arg2: number@scalar, arg3...: any@scalar)
772/// 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}; arg3{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
773/// Caps: PURE
774/// [formualizer-docgen:schema:end]
775impl Function for WorkdayFn {
776    func_caps!(PURE);
777    fn name(&self) -> &'static str {
778        "WORKDAY"
779    }
780    fn min_args(&self) -> usize {
781        2
782    }
783    fn variadic(&self) -> bool {
784        true
785    }
786    fn arg_schema(&self) -> &'static [ArgSchema] {
787        use std::sync::LazyLock;
788        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
789            vec![
790                ArgSchema::number_lenient_scalar(),
791                ArgSchema::number_lenient_scalar(),
792                ArgSchema::any(), // holidays (optional)
793            ]
794        });
795        &SCHEMA[..]
796    }
797    fn eval<'a, 'b, 'c>(
798        &self,
799        args: &'c [ArgumentHandle<'a, 'b>],
800        _ctx: &dyn FunctionContext<'b>,
801    ) -> Result<CalcValue<'b>, ExcelError> {
802        let start_serial = coerce_to_serial(&args[0])?;
803        let days = coerce_to_int(&args[1])?;
804
805        let start_date = serial_to_date(start_serial)?;
806
807        let holidays = collect_holidays(args, 2);
808
809        let mut current = start_date;
810        let mut remaining = days.abs();
811        let direction: i64 = if days >= 0 { 1 } else { -1 };
812
813        while remaining > 0 {
814            current = if direction > 0 {
815                current.succ_opt().ok_or_else(ExcelError::new_num)?
816            } else {
817                current.pred_opt().ok_or_else(ExcelError::new_num)?
818            };
819
820            if !is_weekend(&current) && !holidays.contains(&current) {
821                remaining -= 1;
822            }
823        }
824
825        Ok(CalcValue::Scalar(LiteralValue::Number(date_to_serial(
826            &current,
827        ))))
828    }
829}
830
831/// Returns the number of working days between two dates with configurable weekends.
832///
833/// # Remarks
834/// - The `weekend` argument can be a number code (1-7, 11-17) or a 7-character string
835///   of `0`s and `1`s (Mon-Sun, `1` = weekend day). Default is `1` (Sat/Sun).
836/// - The optional `holidays` argument accepts a range or array of date serials to exclude.
837/// - If `start_date > end_date`, the result is negative.
838/// - A weekend string of all `1`s (no workdays) returns `#VALUE!`.
839///
840/// # Examples
841/// ```excel
842/// =NETWORKDAYS.INTL(DATE(2024,1,1), DATE(2024,1,31))
843/// ```
844///
845/// ```yaml,sandbox
846/// title: "Default weekends (same as NETWORKDAYS)"
847/// formula: "=NETWORKDAYS.INTL(DATE(2024,1,1), DATE(2024,1,31))"
848/// expected: 23
849/// ```
850///
851/// ```yaml,sandbox
852/// title: "Friday-only weekend"
853/// formula: "=NETWORKDAYS.INTL(DATE(2024,1,1), DATE(2024,1,7), 16)"
854/// expected: 6
855/// ```
856///
857/// ```yaml,sandbox
858/// title: "Custom weekend string (Mon+Fri off)"
859/// formula: "=NETWORKDAYS.INTL(DATE(2024,1,1), DATE(2024,1,7), \"1000100\")"
860/// expected: 5
861/// ```
862///
863/// ```yaml,docs
864/// related:
865///   - NETWORKDAYS
866///   - WORKDAY.INTL
867///   - WEEKDAY
868/// faq:
869///   - q: "What happens if the weekend string is all 1s?"
870///     a: "NETWORKDAYS.INTL returns #VALUE! because there would be no workdays."
871/// ```
872#[derive(Debug)]
873pub struct NetworkdaysIntlFn;
874/// [formualizer-docgen:schema:start]
875/// Name: NETWORKDAYS.INTL
876/// Type: NetworkdaysIntlFn
877/// Min args: 2
878/// Max args: variadic
879/// Variadic: true
880/// Signature: NETWORKDAYS.INTL(arg1: number@scalar, arg2: number@scalar, arg3: any@scalar, arg4...: any@scalar)
881/// 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}; arg3{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg4{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
882/// Caps: PURE
883/// [formualizer-docgen:schema:end]
884impl Function for NetworkdaysIntlFn {
885    func_caps!(PURE);
886    fn name(&self) -> &'static str {
887        "NETWORKDAYS.INTL"
888    }
889    fn min_args(&self) -> usize {
890        2
891    }
892    fn variadic(&self) -> bool {
893        true
894    }
895    fn arg_schema(&self) -> &'static [ArgSchema] {
896        use std::sync::LazyLock;
897        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
898            vec![
899                ArgSchema::number_lenient_scalar(), // start_date
900                ArgSchema::number_lenient_scalar(), // end_date
901                ArgSchema::any(),                   // weekend (optional)
902                ArgSchema::any(),                   // holidays (optional)
903            ]
904        });
905        &SCHEMA[..]
906    }
907    fn eval<'a, 'b, 'c>(
908        &self,
909        args: &'c [ArgumentHandle<'a, 'b>],
910        _ctx: &dyn FunctionContext<'b>,
911    ) -> Result<CalcValue<'b>, ExcelError> {
912        let start_serial = coerce_to_serial(&args[0])?;
913        let end_serial = coerce_to_serial(&args[1])?;
914
915        let start_date = serial_to_date(start_serial)?;
916        let end_date = serial_to_date(end_serial)?;
917
918        let mask = if args.len() > 2 {
919            match parse_weekend_mask(&args[2]) {
920                Ok(Some(m)) => m,
921                Ok(None) => {
922                    return Ok(CalcValue::Scalar(
923                        LiteralValue::Error(ExcelError::new_num()),
924                    ));
925                }
926                Err(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
927            }
928        } else {
929            DEFAULT_WEEKEND_MASK
930        };
931
932        let holidays = collect_holidays(args, 3);
933
934        let (start, end, sign) = if start_date <= end_date {
935            (start_date, end_date, 1i64)
936        } else {
937            (end_date, start_date, -1i64)
938        };
939
940        let mut count = 0i64;
941        let mut current = start;
942        while current <= end {
943            if !is_weekend_masked(&current, &mask) && !holidays.contains(&current) {
944                count += 1;
945            }
946            current = current.succ_opt().unwrap_or(current);
947        }
948
949        Ok(CalcValue::Scalar(LiteralValue::Int(count * sign)))
950    }
951}
952
953/// Returns the date serial that is a given number of workdays from a start date,
954/// with configurable weekends.
955///
956/// # Remarks
957/// - Positive `days` moves forward; negative `days` moves backward.
958/// - The `weekend` argument can be a number code (1-7, 11-17) or a 7-character string
959///   of `0`s and `1`s (Mon-Sun, `1` = weekend day). Default is `1` (Sat/Sun).
960/// - The optional `holidays` argument accepts a range or array of date serials to exclude.
961/// - A weekend string of all `1`s (no workdays) returns `#VALUE!`.
962///
963/// # Examples
964/// ```excel
965/// =WORKDAY.INTL(DATE(2024,1,1), 5)
966/// ```
967///
968/// ```yaml,sandbox
969/// title: "Default weekends (same as WORKDAY)"
970/// formula: "=WORKDAY.INTL(DATE(2024,1,1), 5)"
971/// expected: 45306
972/// ```
973///
974/// ```yaml,sandbox
975/// title: "Sunday-only weekend"
976/// formula: "=WORKDAY.INTL(DATE(2024,1,1), 5, 11)"
977/// expected: 45302
978/// ```
979///
980/// ```yaml,sandbox
981/// title: "Custom weekend string"
982/// formula: "=WORKDAY.INTL(DATE(2024,1,1), 5, \"0000011\")"
983/// expected: 45299
984/// ```
985///
986/// ```yaml,docs
987/// related:
988///   - WORKDAY
989///   - NETWORKDAYS.INTL
990///   - WEEKDAY
991/// faq:
992///   - q: "What is the difference between WORKDAY and WORKDAY.INTL?"
993///     a: "WORKDAY.INTL adds a weekend parameter that lets you define which days are non-working, instead of always using Saturday/Sunday."
994/// ```
995#[derive(Debug)]
996pub struct WorkdayIntlFn;
997/// [formualizer-docgen:schema:start]
998/// Name: WORKDAY.INTL
999/// Type: WorkdayIntlFn
1000/// Min args: 2
1001/// Max args: variadic
1002/// Variadic: true
1003/// Signature: WORKDAY.INTL(arg1: number@scalar, arg2: number@scalar, arg3: any@scalar, arg4...: any@scalar)
1004/// 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}; arg3{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg4{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
1005/// Caps: PURE
1006/// [formualizer-docgen:schema:end]
1007impl Function for WorkdayIntlFn {
1008    func_caps!(PURE);
1009    fn name(&self) -> &'static str {
1010        "WORKDAY.INTL"
1011    }
1012    fn min_args(&self) -> usize {
1013        2
1014    }
1015    fn variadic(&self) -> bool {
1016        true
1017    }
1018    fn arg_schema(&self) -> &'static [ArgSchema] {
1019        use std::sync::LazyLock;
1020        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
1021            vec![
1022                ArgSchema::number_lenient_scalar(), // start_date
1023                ArgSchema::number_lenient_scalar(), // days
1024                ArgSchema::any(),                   // weekend (optional)
1025                ArgSchema::any(),                   // holidays (optional)
1026            ]
1027        });
1028        &SCHEMA[..]
1029    }
1030    fn eval<'a, 'b, 'c>(
1031        &self,
1032        args: &'c [ArgumentHandle<'a, 'b>],
1033        _ctx: &dyn FunctionContext<'b>,
1034    ) -> Result<CalcValue<'b>, ExcelError> {
1035        let start_serial = coerce_to_serial(&args[0])?;
1036        let days = coerce_to_int(&args[1])?;
1037
1038        let start_date = serial_to_date(start_serial)?;
1039
1040        let mask = if args.len() > 2 {
1041            match parse_weekend_mask(&args[2]) {
1042                Ok(Some(m)) => m,
1043                Ok(None) => {
1044                    return Ok(CalcValue::Scalar(
1045                        LiteralValue::Error(ExcelError::new_num()),
1046                    ));
1047                }
1048                Err(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
1049            }
1050        } else {
1051            DEFAULT_WEEKEND_MASK
1052        };
1053
1054        let holidays = collect_holidays(args, 3);
1055
1056        let mut current = start_date;
1057        let mut remaining = days.abs();
1058        let direction: i64 = if days >= 0 { 1 } else { -1 };
1059
1060        while remaining > 0 {
1061            current = if direction > 0 {
1062                current.succ_opt().ok_or_else(ExcelError::new_num)?
1063            } else {
1064                current.pred_opt().ok_or_else(ExcelError::new_num)?
1065            };
1066
1067            if !is_weekend_masked(&current, &mask) && !holidays.contains(&current) {
1068                remaining -= 1;
1069            }
1070        }
1071
1072        Ok(CalcValue::Scalar(LiteralValue::Number(date_to_serial(
1073            &current,
1074        ))))
1075    }
1076}
1077
1078pub fn register_builtins() {
1079    use std::sync::Arc;
1080    crate::function_registry::register_function(Arc::new(WeekdayFn));
1081    crate::function_registry::register_function(Arc::new(WeeknumFn));
1082    crate::function_registry::register_function(Arc::new(DatedifFn));
1083    crate::function_registry::register_function(Arc::new(NetworkdaysFn));
1084    crate::function_registry::register_function(Arc::new(WorkdayFn));
1085    crate::function_registry::register_function(Arc::new(NetworkdaysIntlFn));
1086    crate::function_registry::register_function(Arc::new(WorkdayIntlFn));
1087}
1088
1089#[cfg(test)]
1090mod tests {
1091    use super::*;
1092    use crate::test_workbook::TestWorkbook;
1093    use crate::traits::ArgumentHandle;
1094    use formualizer_parse::parser::{ASTNode, ASTNodeType};
1095
1096    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
1097        wb.interpreter()
1098    }
1099    fn lit(v: LiteralValue) -> ASTNode {
1100        ASTNode::new(ASTNodeType::Literal(v), None)
1101    }
1102
1103    #[test]
1104    fn weekday_basic() {
1105        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(WeekdayFn));
1106        let ctx = interp(&wb);
1107        // Jan 1, 2024 is a Monday
1108        // Serial for 2024-01-01: date_to_serial gives us the value
1109        let serial = date_to_serial(&NaiveDate::from_ymd_opt(2024, 1, 1).unwrap());
1110        let n = lit(LiteralValue::Number(serial));
1111        let f = ctx.context.get_function("", "WEEKDAY").unwrap();
1112        // Default return_type=1: Monday=2
1113        assert_eq!(
1114            f.dispatch(
1115                &[ArgumentHandle::new(&n, &ctx)],
1116                &ctx.function_context(None)
1117            )
1118            .unwrap()
1119            .into_literal(),
1120            LiteralValue::Int(2)
1121        );
1122    }
1123
1124    #[test]
1125    fn datedif_years() {
1126        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(DatedifFn));
1127        let ctx = interp(&wb);
1128        let start = date_to_serial(&NaiveDate::from_ymd_opt(2020, 1, 1).unwrap());
1129        let end = date_to_serial(&NaiveDate::from_ymd_opt(2024, 1, 1).unwrap());
1130        let s = lit(LiteralValue::Number(start));
1131        let e = lit(LiteralValue::Number(end));
1132        let unit = lit(LiteralValue::Text("Y".to_string()));
1133        let f = ctx.context.get_function("", "DATEDIF").unwrap();
1134        assert_eq!(
1135            f.dispatch(
1136                &[
1137                    ArgumentHandle::new(&s, &ctx),
1138                    ArgumentHandle::new(&e, &ctx),
1139                    ArgumentHandle::new(&unit, &ctx)
1140                ],
1141                &ctx.function_context(None)
1142            )
1143            .unwrap()
1144            .into_literal(),
1145            LiteralValue::Int(4)
1146        );
1147    }
1148
1149    // ── Weekend mask helpers ──
1150
1151    #[test]
1152    fn weekend_mask_from_code_default() {
1153        let m = weekend_mask_from_code(1).unwrap();
1154        // Sat(5) and Sun(6) should be true
1155        assert!(!m[0]); // Mon
1156        assert!(!m[4]); // Fri
1157        assert!(m[5]); // Sat
1158        assert!(m[6]); // Sun
1159    }
1160
1161    #[test]
1162    fn weekend_mask_from_code_sunday_only() {
1163        let m = weekend_mask_from_code(11).unwrap();
1164        assert!(m[6]); // Sun
1165        for weekend in m.iter().take(6) {
1166            assert!(!weekend);
1167        }
1168    }
1169
1170    #[test]
1171    fn weekend_mask_from_code_invalid() {
1172        assert!(weekend_mask_from_code(0).is_none());
1173        assert!(weekend_mask_from_code(8).is_none());
1174        assert!(weekend_mask_from_code(18).is_none());
1175    }
1176
1177    #[test]
1178    fn weekend_mask_from_string_basic() {
1179        // Mon+Fri off
1180        let m = weekend_mask_from_string("1000100").unwrap();
1181        assert!(m[0]); // Mon
1182        assert!(!m[1]);
1183        assert!(m[4]); // Fri
1184        assert!(!m[5]);
1185    }
1186
1187    #[test]
1188    fn weekend_mask_from_string_all_ones_invalid() {
1189        assert!(weekend_mask_from_string("1111111").is_none());
1190    }
1191
1192    #[test]
1193    fn weekend_mask_from_string_wrong_length() {
1194        assert!(weekend_mask_from_string("000011").is_none());
1195        assert!(weekend_mask_from_string("00001100").is_none());
1196    }
1197
1198    #[test]
1199    fn weekend_mask_from_string_bad_chars() {
1200        assert!(weekend_mask_from_string("000012X").is_none());
1201    }
1202
1203    #[test]
1204    fn is_weekend_masked_basic() {
1205        let mask = weekend_mask_from_code(1).unwrap(); // Sat+Sun
1206        let mon = NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(); // Monday
1207        let sat = NaiveDate::from_ymd_opt(2024, 1, 6).unwrap(); // Saturday
1208        let sun = NaiveDate::from_ymd_opt(2024, 1, 7).unwrap(); // Sunday
1209        assert!(!is_weekend_masked(&mon, &mask));
1210        assert!(is_weekend_masked(&sat, &mask));
1211        assert!(is_weekend_masked(&sun, &mask));
1212    }
1213
1214    // ── NETWORKDAYS.INTL unit tests ──
1215
1216    #[test]
1217    fn networkdays_intl_default_matches_networkdays() {
1218        // Jan 1-31 2024: NETWORKDAYS gives 23
1219        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NetworkdaysIntlFn));
1220        let ctx = interp(&wb);
1221        let s = lit(LiteralValue::Number(date_to_serial(
1222            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1223        )));
1224        let e = lit(LiteralValue::Number(date_to_serial(
1225            &NaiveDate::from_ymd_opt(2024, 1, 31).unwrap(),
1226        )));
1227        let f = ctx.context.get_function("", "NETWORKDAYS.INTL").unwrap();
1228        let result = f
1229            .dispatch(
1230                &[ArgumentHandle::new(&s, &ctx), ArgumentHandle::new(&e, &ctx)],
1231                &ctx.function_context(None),
1232            )
1233            .unwrap()
1234            .into_literal();
1235        assert_eq!(result, LiteralValue::Int(23));
1236    }
1237
1238    #[test]
1239    fn networkdays_intl_sunday_only_weekend() {
1240        // Jan 1-7 2024 (Mon-Sun): code 11 = Sun only weekend => 6 workdays
1241        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NetworkdaysIntlFn));
1242        let ctx = interp(&wb);
1243        let s = lit(LiteralValue::Number(date_to_serial(
1244            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1245        )));
1246        let e = lit(LiteralValue::Number(date_to_serial(
1247            &NaiveDate::from_ymd_opt(2024, 1, 7).unwrap(),
1248        )));
1249        let wk = lit(LiteralValue::Int(11));
1250        let f = ctx.context.get_function("", "NETWORKDAYS.INTL").unwrap();
1251        let result = f
1252            .dispatch(
1253                &[
1254                    ArgumentHandle::new(&s, &ctx),
1255                    ArgumentHandle::new(&e, &ctx),
1256                    ArgumentHandle::new(&wk, &ctx),
1257                ],
1258                &ctx.function_context(None),
1259            )
1260            .unwrap()
1261            .into_literal();
1262        assert_eq!(result, LiteralValue::Int(6));
1263    }
1264
1265    #[test]
1266    fn networkdays_intl_string_mask() {
1267        // Jan 1-7 2024: "0000011" = Sat+Sun off (same as default) => 5 workdays
1268        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NetworkdaysIntlFn));
1269        let ctx = interp(&wb);
1270        let s = lit(LiteralValue::Number(date_to_serial(
1271            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1272        )));
1273        let e = lit(LiteralValue::Number(date_to_serial(
1274            &NaiveDate::from_ymd_opt(2024, 1, 7).unwrap(),
1275        )));
1276        let wk = lit(LiteralValue::Text("0000011".to_string()));
1277        let f = ctx.context.get_function("", "NETWORKDAYS.INTL").unwrap();
1278        let result = f
1279            .dispatch(
1280                &[
1281                    ArgumentHandle::new(&s, &ctx),
1282                    ArgumentHandle::new(&e, &ctx),
1283                    ArgumentHandle::new(&wk, &ctx),
1284                ],
1285                &ctx.function_context(None),
1286            )
1287            .unwrap()
1288            .into_literal();
1289        assert_eq!(result, LiteralValue::Int(5));
1290    }
1291
1292    #[test]
1293    fn networkdays_intl_invalid_code_returns_num_error() {
1294        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NetworkdaysIntlFn));
1295        let ctx = interp(&wb);
1296        let s = lit(LiteralValue::Number(date_to_serial(
1297            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1298        )));
1299        let e = lit(LiteralValue::Number(date_to_serial(
1300            &NaiveDate::from_ymd_opt(2024, 1, 7).unwrap(),
1301        )));
1302        let wk = lit(LiteralValue::Int(99));
1303        let f = ctx.context.get_function("", "NETWORKDAYS.INTL").unwrap();
1304        let result = f
1305            .dispatch(
1306                &[
1307                    ArgumentHandle::new(&s, &ctx),
1308                    ArgumentHandle::new(&e, &ctx),
1309                    ArgumentHandle::new(&wk, &ctx),
1310                ],
1311                &ctx.function_context(None),
1312            )
1313            .unwrap()
1314            .into_literal();
1315        assert!(matches!(result, LiteralValue::Error(_)));
1316    }
1317
1318    #[test]
1319    fn networkdays_intl_all_weekends_string_returns_value_error() {
1320        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NetworkdaysIntlFn));
1321        let ctx = interp(&wb);
1322        let s = lit(LiteralValue::Number(date_to_serial(
1323            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1324        )));
1325        let e = lit(LiteralValue::Number(date_to_serial(
1326            &NaiveDate::from_ymd_opt(2024, 1, 7).unwrap(),
1327        )));
1328        let wk = lit(LiteralValue::Text("1111111".to_string()));
1329        let f = ctx.context.get_function("", "NETWORKDAYS.INTL").unwrap();
1330        let result = f
1331            .dispatch(
1332                &[
1333                    ArgumentHandle::new(&s, &ctx),
1334                    ArgumentHandle::new(&e, &ctx),
1335                    ArgumentHandle::new(&wk, &ctx),
1336                ],
1337                &ctx.function_context(None),
1338            )
1339            .unwrap()
1340            .into_literal();
1341        match result {
1342            LiteralValue::Error(err) => {
1343                assert_eq!(err.kind, formualizer_common::ExcelErrorKind::Value)
1344            }
1345            other => panic!("expected #VALUE! error, got {other:?}"),
1346        }
1347    }
1348
1349    #[test]
1350    fn networkdays_collects_inline_array_holidays() {
1351        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NetworkdaysIntlFn));
1352        let ctx = interp(&wb);
1353        let s = lit(LiteralValue::Number(date_to_serial(
1354            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1355        )));
1356        let e = lit(LiteralValue::Number(date_to_serial(
1357            &NaiveDate::from_ymd_opt(2024, 1, 7).unwrap(),
1358        )));
1359        let wk = lit(LiteralValue::Int(1));
1360        let holidays = lit(LiteralValue::Array(vec![vec![
1361            LiteralValue::Number(date_to_serial(
1362                &NaiveDate::from_ymd_opt(2024, 1, 2).unwrap(),
1363            )),
1364            LiteralValue::Number(date_to_serial(
1365                &NaiveDate::from_ymd_opt(2024, 1, 3).unwrap(),
1366            )),
1367        ]]));
1368        let f = ctx.context.get_function("", "NETWORKDAYS.INTL").unwrap();
1369        let result = f
1370            .dispatch(
1371                &[
1372                    ArgumentHandle::new(&s, &ctx),
1373                    ArgumentHandle::new(&e, &ctx),
1374                    ArgumentHandle::new(&wk, &ctx),
1375                    ArgumentHandle::new(&holidays, &ctx),
1376                ],
1377                &ctx.function_context(None),
1378            )
1379            .unwrap()
1380            .into_literal();
1381        assert_eq!(result, LiteralValue::Int(3));
1382    }
1383
1384    // ── WORKDAY.INTL unit tests ──
1385
1386    #[test]
1387    fn workday_intl_default_matches_workday() {
1388        // WORKDAY(2024-01-01, 10) = 45306
1389        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(WorkdayIntlFn));
1390        let ctx = interp(&wb);
1391        let s = lit(LiteralValue::Number(date_to_serial(
1392            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1393        )));
1394        let d = lit(LiteralValue::Int(10));
1395        let f = ctx.context.get_function("", "WORKDAY.INTL").unwrap();
1396        let result = f
1397            .dispatch(
1398                &[ArgumentHandle::new(&s, &ctx), ArgumentHandle::new(&d, &ctx)],
1399                &ctx.function_context(None),
1400            )
1401            .unwrap()
1402            .into_literal();
1403        // serial for 2024-01-15 (Mon) = 45306
1404        assert_eq!(result, LiteralValue::Number(45306.0));
1405    }
1406
1407    #[test]
1408    fn workday_intl_sunday_only() {
1409        // 2024-01-01 is Monday. code 11 = Sunday only weekend.
1410        // 5 workdays forward: Tue..Sat all work, so 5 days = Jan 6 (Sat)
1411        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(WorkdayIntlFn));
1412        let ctx = interp(&wb);
1413        let s = lit(LiteralValue::Number(date_to_serial(
1414            &NaiveDate::from_ymd_opt(2024, 1, 1).unwrap(),
1415        )));
1416        let d = lit(LiteralValue::Int(5));
1417        let wk = lit(LiteralValue::Int(11));
1418        let f = ctx.context.get_function("", "WORKDAY.INTL").unwrap();
1419        let result = f
1420            .dispatch(
1421                &[
1422                    ArgumentHandle::new(&s, &ctx),
1423                    ArgumentHandle::new(&d, &ctx),
1424                    ArgumentHandle::new(&wk, &ctx),
1425                ],
1426                &ctx.function_context(None),
1427            )
1428            .unwrap()
1429            .into_literal();
1430        let expected = date_to_serial(&NaiveDate::from_ymd_opt(2024, 1, 6).unwrap());
1431        assert_eq!(result, LiteralValue::Number(expected));
1432    }
1433
1434    #[test]
1435    fn workday_intl_backward() {
1436        // 2024-01-15 (Mon) minus 5 workdays with default weekends = 2024-01-08 (Mon)
1437        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(WorkdayIntlFn));
1438        let ctx = interp(&wb);
1439        let s = lit(LiteralValue::Number(date_to_serial(
1440            &NaiveDate::from_ymd_opt(2024, 1, 15).unwrap(),
1441        )));
1442        let d = lit(LiteralValue::Int(-5));
1443        let f = ctx.context.get_function("", "WORKDAY.INTL").unwrap();
1444        let result = f
1445            .dispatch(
1446                &[ArgumentHandle::new(&s, &ctx), ArgumentHandle::new(&d, &ctx)],
1447                &ctx.function_context(None),
1448            )
1449            .unwrap()
1450            .into_literal();
1451        let expected = date_to_serial(&NaiveDate::from_ymd_opt(2024, 1, 8).unwrap());
1452        assert_eq!(result, LiteralValue::Number(expected));
1453    }
1454}
formualizer_eval/builtins/datetime/weekday_workday.rs

formualizer_eval/builtins/datetime/
weekday_workday.rs
