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

formualizer_eval/builtins/datetime/
weekday_workday.rs
