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
11fn coerce_to_serial(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
12 let v = arg.value()?.into_literal();
13 match v {
14 LiteralValue::Number(f) => Ok(f),
15 LiteralValue::Int(i) => Ok(i as f64),
16 LiteralValue::Date(d) => Ok(date_to_serial(&d)),
17 LiteralValue::DateTime(dt) => Ok(date_to_serial(&dt.date())),
18 LiteralValue::Text(s) => s
19 .parse::<f64>()
20 .map_err(|_| ExcelError::new_value().with_message("Not a valid number")),
21 LiteralValue::Boolean(b) => Ok(if b { 1.0 } else { 0.0 }),
22 LiteralValue::Empty => Ok(0.0),
23 LiteralValue::Error(e) => Err(e),
24 _ => Err(ExcelError::new_value()),
25 }
26}
27
28fn coerce_to_int(arg: &ArgumentHandle) -> Result<i64, ExcelError> {
29 let v = arg.value()?.into_literal();
30 match v {
31 LiteralValue::Number(f) => Ok(f.trunc() as i64),
32 LiteralValue::Int(i) => Ok(i),
33 LiteralValue::Boolean(b) => Ok(if b { 1 } else { 0 }),
34 LiteralValue::Empty => Ok(0),
35 LiteralValue::Error(e) => Err(e),
36 _ => Err(ExcelError::new_value()),
37 }
38}
39
40/// Returns the day-of-week index for a date serial with configurable numbering.
41///
42/// # Remarks
43/// - Default `return_type` is `1` (`Sunday=1` through `Saturday=7`).
44/// - Supported `return_type` values are `1`, `2`, `3`, `11`-`17`; unsupported values return `#NUM!`.
45/// - Input serials are interpreted with Excel 1900 date mapping, including its historical leap-year quirk.
46///
47/// # Examples
48/// ```yaml,sandbox
49/// title: "Default numbering (Sunday-first)"
50/// formula: "=WEEKDAY(45292)"
51/// expected: 2
52/// ```
53///
54/// ```yaml,sandbox
55/// title: "Monday-first numbering"
56/// formula: "=WEEKDAY(45292, 2)"
57/// expected: 1
58/// ```
59///
60/// ```yaml,docs
61/// related:
62/// - WEEKNUM
63/// - ISOWEEKNUM
64/// - WORKDAY
65/// faq:
66/// - q: "Why do I get #NUM! for some return_type values?"
67/// a: "WEEKDAY only accepts specific Excel return_type codes (1, 2, 3, 11-17); other codes return #NUM!."
68/// ```
69#[derive(Debug)]
70pub struct WeekdayFn;
71/// [formualizer-docgen:schema:start]
72/// Name: WEEKDAY
73/// Type: WeekdayFn
74/// Min args: 1
75/// Max args: variadic
76/// Variadic: true
77/// Signature: WEEKDAY(arg1: number@scalar, arg2...: number@scalar)
78/// 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}
79/// Caps: PURE
80/// [formualizer-docgen:schema:end]
81impl Function for WeekdayFn {
82 func_caps!(PURE);
83 fn name(&self) -> &'static str {
84 "WEEKDAY"
85 }
86 fn min_args(&self) -> usize {
87 1
88 }
89 fn variadic(&self) -> bool {
90 true
91 }
92 fn arg_schema(&self) -> &'static [ArgSchema] {
93 use std::sync::LazyLock;
94 static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
95 vec![
96 ArgSchema::number_lenient_scalar(),
97 ArgSchema::number_lenient_scalar(),
98 ]
99 });
100 &SCHEMA[..]
101 }
102 fn eval<'a, 'b, 'c>(
103 &self,
104 args: &'c [ArgumentHandle<'a, 'b>],
105 _ctx: &dyn FunctionContext<'b>,
106 ) -> Result<CalcValue<'b>, ExcelError> {
107 let serial = coerce_to_serial(&args[0])?;
108 let return_type = if args.len() > 1 {
109 coerce_to_int(&args[1])?
110 } else {
111 1
112 };
113
114 let date = serial_to_date(serial)?;
115 let weekday = date.weekday();
116
117 // Convert chrono weekday (Mon=0..Sun=6) to Excel format
118 let result = match return_type {
119 1 => match weekday {
120 Weekday::Sun => 1,
121 Weekday::Mon => 2,
122 Weekday::Tue => 3,
123 Weekday::Wed => 4,
124 Weekday::Thu => 5,
125 Weekday::Fri => 6,
126 Weekday::Sat => 7,
127 },
128 2 => match weekday {
129 Weekday::Mon => 1,
130 Weekday::Tue => 2,
131 Weekday::Wed => 3,
132 Weekday::Thu => 4,
133 Weekday::Fri => 5,
134 Weekday::Sat => 6,
135 Weekday::Sun => 7,
136 },
137 3 => match weekday {
138 Weekday::Mon => 0,
139 Weekday::Tue => 1,
140 Weekday::Wed => 2,
141 Weekday::Thu => 3,
142 Weekday::Fri => 4,
143 Weekday::Sat => 5,
144 Weekday::Sun => 6,
145 },
146 11 => match weekday {
147 // Mon=1..Sun=7
148 Weekday::Mon => 1,
149 Weekday::Tue => 2,
150 Weekday::Wed => 3,
151 Weekday::Thu => 4,
152 Weekday::Fri => 5,
153 Weekday::Sat => 6,
154 Weekday::Sun => 7,
155 },
156 12 => match weekday {
157 // Tue=1..Mon=7
158 Weekday::Tue => 1,
159 Weekday::Wed => 2,
160 Weekday::Thu => 3,
161 Weekday::Fri => 4,
162 Weekday::Sat => 5,
163 Weekday::Sun => 6,
164 Weekday::Mon => 7,
165 },
166 13 => match weekday {
167 // Wed=1..Tue=7
168 Weekday::Wed => 1,
169 Weekday::Thu => 2,
170 Weekday::Fri => 3,
171 Weekday::Sat => 4,
172 Weekday::Sun => 5,
173 Weekday::Mon => 6,
174 Weekday::Tue => 7,
175 },
176 14 => match weekday {
177 // Thu=1..Wed=7
178 Weekday::Thu => 1,
179 Weekday::Fri => 2,
180 Weekday::Sat => 3,
181 Weekday::Sun => 4,
182 Weekday::Mon => 5,
183 Weekday::Tue => 6,
184 Weekday::Wed => 7,
185 },
186 15 => match weekday {
187 // Fri=1..Thu=7
188 Weekday::Fri => 1,
189 Weekday::Sat => 2,
190 Weekday::Sun => 3,
191 Weekday::Mon => 4,
192 Weekday::Tue => 5,
193 Weekday::Wed => 6,
194 Weekday::Thu => 7,
195 },
196 16 => match weekday {
197 // Sat=1..Fri=7
198 Weekday::Sat => 1,
199 Weekday::Sun => 2,
200 Weekday::Mon => 3,
201 Weekday::Tue => 4,
202 Weekday::Wed => 5,
203 Weekday::Thu => 6,
204 Weekday::Fri => 7,
205 },
206 17 => match weekday {
207 // Sun=1..Sat=7
208 Weekday::Sun => 1,
209 Weekday::Mon => 2,
210 Weekday::Tue => 3,
211 Weekday::Wed => 4,
212 Weekday::Thu => 5,
213 Weekday::Fri => 6,
214 Weekday::Sat => 7,
215 },
216 _ => {
217 return Ok(CalcValue::Scalar(
218 LiteralValue::Error(ExcelError::new_num()),
219 ));
220 }
221 };
222
223 Ok(CalcValue::Scalar(LiteralValue::Int(result)))
224 }
225}
226
227/// Returns the week number of the year for a date serial.
228///
229/// # Remarks
230/// - Default `return_type` is `1` (week starts on Sunday).
231/// - Supported `return_type` values are `1`, `2`, `11`-`17`, and `21` (ISO week numbering).
232/// - Unsupported `return_type` values return `#NUM!`.
233/// - Input serials are interpreted using Excel 1900 date mapping rather than workbook `1904` interpretation.
234///
235/// # Examples
236/// ```yaml,sandbox
237/// title: "Default week numbering"
238/// formula: "=WEEKNUM(45292)"
239/// expected: 1
240/// ```
241///
242/// ```yaml,sandbox
243/// title: "ISO week numbering"
244/// formula: "=WEEKNUM(42370, 21)"
245/// expected: 53
246/// ```
247///
248/// ```yaml,docs
249/// related:
250/// - WEEKDAY
251/// - ISOWEEKNUM
252/// - DATE
253/// faq:
254/// - q: "What is special about return_type 21 in WEEKNUM?"
255/// a: "return_type=21 switches to ISO week numbering, matching ISOWEEKNUM behavior."
256/// ```
257#[derive(Debug)]
258pub struct WeeknumFn;
259/// [formualizer-docgen:schema:start]
260/// Name: WEEKNUM
261/// Type: WeeknumFn
262/// Min args: 1
263/// Max args: variadic
264/// Variadic: true
265/// Signature: WEEKNUM(arg1: number@scalar, arg2...: number@scalar)
266/// 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}
267/// Caps: PURE
268/// [formualizer-docgen:schema:end]
269impl Function for WeeknumFn {
270 func_caps!(PURE);
271 fn name(&self) -> &'static str {
272 "WEEKNUM"
273 }
274 fn min_args(&self) -> usize {
275 1
276 }
277 fn variadic(&self) -> bool {
278 true
279 }
280 fn arg_schema(&self) -> &'static [ArgSchema] {
281 use std::sync::LazyLock;
282 static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
283 vec![
284 ArgSchema::number_lenient_scalar(),
285 ArgSchema::number_lenient_scalar(),
286 ]
287 });
288 &SCHEMA[..]
289 }
290 fn eval<'a, 'b, 'c>(
291 &self,
292 args: &'c [ArgumentHandle<'a, 'b>],
293 _ctx: &dyn FunctionContext<'b>,
294 ) -> Result<CalcValue<'b>, ExcelError> {
295 let serial = coerce_to_serial(&args[0])?;
296 let return_type = if args.len() > 1 {
297 coerce_to_int(&args[1])?
298 } else {
299 1
300 };
301
302 let date = serial_to_date(serial)?;
303
304 // Determine first day of week
305 let week_starts = match return_type {
306 1 | 17 => Weekday::Sun,
307 2 | 11 => Weekday::Mon,
308 12 => Weekday::Tue,
309 13 => Weekday::Wed,
310 14 => Weekday::Thu,
311 15 => Weekday::Fri,
312 16 => Weekday::Sat,
313 21 => {
314 // ISO week number (special case)
315 return Ok(CalcValue::Scalar(LiteralValue::Int(
316 date.iso_week().week() as i64
317 )));
318 }
319 _ => {
320 return Ok(CalcValue::Scalar(
321 LiteralValue::Error(ExcelError::new_num()),
322 ));
323 }
324 };
325
326 // Calculate week number based on when week starts
327 let jan1 = NaiveDate::from_ymd_opt(date.year(), 1, 1).unwrap();
328 let jan1_weekday = jan1.weekday();
329
330 // Days from week start day to Jan 1
331 let days_to_week_start = |wd: Weekday| -> i64 {
332 let target = week_starts.num_days_from_sunday() as i64;
333 let current = wd.num_days_from_sunday() as i64;
334 (current - target + 7) % 7
335 };
336
337 let jan1_offset = days_to_week_start(jan1_weekday);
338 let day_of_year = date.ordinal() as i64;
339
340 // Week 1 starts on the first occurrence of week_starts day, or Jan 1 if it is that day
341 let week_num = if jan1_offset == 0 {
342 (day_of_year - 1) / 7 + 1
343 } else {
344 (day_of_year + jan1_offset - 1) / 7 + 1
345 };
346
347 Ok(CalcValue::Scalar(LiteralValue::Int(week_num)))
348 }
349}
350
351/// Returns the difference between two dates in a requested unit.
352///
353/// # Remarks
354/// - Supported units are `"Y"`, `"M"`, `"D"`, `"MD"`, `"YM"`, and `"YD"`.
355/// - If `start_date > end_date`, the function returns `#NUM!`.
356/// - Unit matching is case-insensitive.
357/// - `"YD"` uses a Feb-29 normalization strategy that can differ slightly from Excel in edge cases.
358/// - Input serials are interpreted with Excel 1900 date mapping.
359///
360/// # Examples
361/// ```yaml,sandbox
362/// title: "Difference in days"
363/// formula: '=DATEDIF(44197, 44378, "D")'
364/// expected: 181
365/// ```
366///
367/// ```yaml,sandbox
368/// title: "Complete months difference"
369/// formula: '=DATEDIF(44197, 44378, "M")'
370/// expected: 6
371/// ```
372///
373/// ```yaml,docs
374/// related:
375/// - DAYS
376/// - YEARFRAC
377/// - DATE
378/// faq:
379/// - q: "How are unit strings interpreted in DATEDIF?"
380/// a: "Unit text is case-insensitive, but only Y, M, D, MD, YM, and YD are supported; other units return #NUM!."
381/// ```
382#[derive(Debug)]
383pub struct DatedifFn;
384/// [formualizer-docgen:schema:start]
385/// Name: DATEDIF
386/// Type: DatedifFn
387/// Min args: 3
388/// Max args: 3
389/// Variadic: false
390/// Signature: DATEDIF(arg1: number@scalar, arg2: number@scalar, arg3: any@scalar)
391/// 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}
392/// Caps: PURE
393/// [formualizer-docgen:schema:end]
394impl Function for DatedifFn {
395 func_caps!(PURE);
396 fn name(&self) -> &'static str {
397 "DATEDIF"
398 }
399 fn min_args(&self) -> usize {
400 3
401 }
402 fn arg_schema(&self) -> &'static [ArgSchema] {
403 use std::sync::LazyLock;
404 static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
405 vec![
406 ArgSchema::number_lenient_scalar(),
407 ArgSchema::number_lenient_scalar(),
408 ArgSchema::any(),
409 ]
410 });
411 &SCHEMA[..]
412 }
413 fn eval<'a, 'b, 'c>(
414 &self,
415 args: &'c [ArgumentHandle<'a, 'b>],
416 _ctx: &dyn FunctionContext<'b>,
417 ) -> Result<CalcValue<'b>, ExcelError> {
418 let start_serial = coerce_to_serial(&args[0])?;
419 let end_serial = coerce_to_serial(&args[1])?;
420
421 let unit = match args[2].value()?.into_literal() {
422 LiteralValue::Text(s) => s.to_uppercase(),
423 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
424 _ => {
425 return Ok(CalcValue::Scalar(LiteralValue::Error(
426 ExcelError::new_value(),
427 )));
428 }
429 };
430
431 if start_serial > end_serial {
432 return Ok(CalcValue::Scalar(
433 LiteralValue::Error(ExcelError::new_num()),
434 ));
435 }
436
437 let start_date = serial_to_date(start_serial)?;
438 let end_date = serial_to_date(end_serial)?;
439
440 let result = match unit.as_str() {
441 "Y" => {
442 // Complete years
443 let mut years = end_date.year() - start_date.year();
444 if (end_date.month(), end_date.day()) < (start_date.month(), start_date.day()) {
445 years -= 1;
446 }
447 years as i64
448 }
449 "M" => {
450 // Complete months
451 let mut months = (end_date.year() - start_date.year()) * 12
452 + (end_date.month() as i32 - start_date.month() as i32);
453 if end_date.day() < start_date.day() {
454 months -= 1;
455 }
456 months as i64
457 }
458 "D" => {
459 // Days
460 (end_date - start_date).num_days()
461 }
462 "MD" => {
463 // Days ignoring months and years
464 let mut days = end_date.day() as i64 - start_date.day() as i64;
465 if days < 0 {
466 // Get days in the previous month
467 let prev_month = if end_date.month() == 1 {
468 NaiveDate::from_ymd_opt(end_date.year() - 1, 12, 1)
469 } else {
470 NaiveDate::from_ymd_opt(end_date.year(), end_date.month() - 1, 1)
471 }
472 .unwrap();
473 let days_in_prev_month = (NaiveDate::from_ymd_opt(
474 if prev_month.month() == 12 {
475 prev_month.year() + 1
476 } else {
477 prev_month.year()
478 },
479 if prev_month.month() == 12 {
480 1
481 } else {
482 prev_month.month() + 1
483 },
484 1,
485 )
486 .unwrap()
487 - prev_month)
488 .num_days();
489 days += days_in_prev_month;
490 }
491 days
492 }
493 "YM" => {
494 // Months ignoring years
495 let mut months = end_date.month() as i64 - start_date.month() as i64;
496 if end_date.day() < start_date.day() {
497 months -= 1;
498 }
499 if months < 0 {
500 months += 12;
501 }
502 months
503 }
504 "YD" => {
505 // Days ignoring years
506 // NOTE: Known edge case - uses .min(28) for Feb 29 handling which may differ from Excel
507 let start_in_end_year = NaiveDate::from_ymd_opt(
508 end_date.year(),
509 start_date.month(),
510 start_date.day().min(28), // Handle Feb 29 -> Feb 28
511 );
512 match start_in_end_year {
513 Some(d) if d <= end_date => (end_date - d).num_days(),
514 _ => {
515 // Start date would be after end date in same year, use previous year
516 let start_prev_year = NaiveDate::from_ymd_opt(
517 end_date.year() - 1,
518 start_date.month(),
519 start_date.day().min(28),
520 )
521 .unwrap();
522 (end_date - start_prev_year).num_days()
523 }
524 }
525 }
526 _ => {
527 return Ok(CalcValue::Scalar(
528 LiteralValue::Error(ExcelError::new_num()),
529 ));
530 }
531 };
532
533 Ok(CalcValue::Scalar(LiteralValue::Int(result)))
534 }
535}
536
537/// Helper: check if a date is a weekend (Saturday or Sunday)
538fn is_weekend(date: &NaiveDate) -> bool {
539 matches!(date.weekday(), Weekday::Sat | Weekday::Sun)
540}
541
542/// Returns the number of weekday business days between two dates, inclusive.
543///
544/// # Remarks
545/// - Weekends are fixed to Saturday and Sunday.
546/// - If `start_date > end_date`, the result is negative.
547/// - The optional `holidays` argument is currently accepted but ignored; holiday exclusions are not yet supported.
548/// - Input serials are interpreted with Excel 1900 date mapping.
549///
550/// # Examples
551/// ```yaml,sandbox
552/// title: "Count weekdays in a range"
553/// formula: "=NETWORKDAYS(45292, 45299)"
554/// expected: 6
555/// ```
556///
557/// ```yaml,sandbox
558/// title: "Holiday argument currently has no effect"
559/// formula: "=NETWORKDAYS(45292, 45299, 45293)"
560/// expected: 6
561/// ```
562///
563/// ```yaml,docs
564/// related:
565/// - WORKDAY
566/// - WEEKDAY
567/// - DAYS
568/// faq:
569/// - q: "Are custom holidays excluded in NETWORKDAYS right now?"
570/// a: "Not yet. The third argument is accepted but currently ignored, so only Saturday/Sunday weekends are excluded."
571/// ```
572#[derive(Debug)]
573pub struct NetworkdaysFn;
574/// [formualizer-docgen:schema:start]
575/// Name: NETWORKDAYS
576/// Type: NetworkdaysFn
577/// Min args: 2
578/// Max args: variadic
579/// Variadic: true
580/// Signature: NETWORKDAYS(arg1: number@scalar, arg2: number@scalar, arg3...: any@scalar)
581/// 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}
582/// Caps: PURE
583/// [formualizer-docgen:schema:end]
584impl Function for NetworkdaysFn {
585 func_caps!(PURE);
586 fn name(&self) -> &'static str {
587 "NETWORKDAYS"
588 }
589 fn min_args(&self) -> usize {
590 2
591 }
592 fn variadic(&self) -> bool {
593 true
594 }
595 fn arg_schema(&self) -> &'static [ArgSchema] {
596 use std::sync::LazyLock;
597 static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
598 vec![
599 ArgSchema::number_lenient_scalar(),
600 ArgSchema::number_lenient_scalar(),
601 ArgSchema::any(), // holidays (optional)
602 ]
603 });
604 &SCHEMA[..]
605 }
606 fn eval<'a, 'b, 'c>(
607 &self,
608 args: &'c [ArgumentHandle<'a, 'b>],
609 _ctx: &dyn FunctionContext<'b>,
610 ) -> Result<CalcValue<'b>, ExcelError> {
611 let start_serial = coerce_to_serial(&args[0])?;
612 let end_serial = coerce_to_serial(&args[1])?;
613
614 let start_date = serial_to_date(start_serial)?;
615 let end_date = serial_to_date(end_serial)?;
616
617 // Collect holidays if provided
618 // TODO: Implement holiday array support
619 let holidays: Vec<NaiveDate> = if args.len() > 2 {
620 // For now, skip holiday handling (would need array support)
621 vec![]
622 } else {
623 vec![]
624 };
625
626 let (start, end, sign) = if start_date <= end_date {
627 (start_date, end_date, 1i64)
628 } else {
629 (end_date, start_date, -1i64)
630 };
631
632 let mut count = 0i64;
633 let mut current = start;
634 while current <= end {
635 if !is_weekend(¤t) && !holidays.contains(¤t) {
636 count += 1;
637 }
638 current = current.succ_opt().unwrap_or(current);
639 }
640
641 Ok(CalcValue::Scalar(LiteralValue::Int(count * sign)))
642 }
643}
644
645/// Returns the date serial that is a given number of weekdays from a start date.
646///
647/// # Remarks
648/// - Positive `days` moves forward; negative `days` moves backward.
649/// - Weekends are fixed to Saturday and Sunday.
650/// - The optional `holidays` argument is currently accepted but ignored; holiday exclusions are not yet supported.
651/// - Input and output serials use Excel 1900 date mapping.
652///
653/// # Examples
654/// ```yaml,sandbox
655/// title: "Move forward by five workdays"
656/// formula: "=WORKDAY(45292, 5)"
657/// expected: 45299
658/// ```
659///
660/// ```yaml,sandbox
661/// title: "Holiday argument currently has no effect"
662/// formula: "=WORKDAY(45292, 5, 45293)"
663/// expected: 45299
664/// ```
665///
666/// ```yaml,docs
667/// related:
668/// - NETWORKDAYS
669/// - WEEKDAY
670/// - TODAY
671/// faq:
672/// - q: "Does WORKDAY include the start date when days=0?"
673/// a: "Yes. With zero offset, WORKDAY returns the start date serial unchanged; nonzero offsets skip weekend days while stepping."
674/// ```
675#[derive(Debug)]
676pub struct WorkdayFn;
677/// [formualizer-docgen:schema:start]
678/// Name: WORKDAY
679/// Type: WorkdayFn
680/// Min args: 2
681/// Max args: variadic
682/// Variadic: true
683/// Signature: WORKDAY(arg1: number@scalar, arg2: number@scalar, arg3...: any@scalar)
684/// 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}
685/// Caps: PURE
686/// [formualizer-docgen:schema:end]
687impl Function for WorkdayFn {
688 func_caps!(PURE);
689 fn name(&self) -> &'static str {
690 "WORKDAY"
691 }
692 fn min_args(&self) -> usize {
693 2
694 }
695 fn variadic(&self) -> bool {
696 true
697 }
698 fn arg_schema(&self) -> &'static [ArgSchema] {
699 use std::sync::LazyLock;
700 static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
701 vec![
702 ArgSchema::number_lenient_scalar(),
703 ArgSchema::number_lenient_scalar(),
704 ArgSchema::any(), // holidays (optional)
705 ]
706 });
707 &SCHEMA[..]
708 }
709 fn eval<'a, 'b, 'c>(
710 &self,
711 args: &'c [ArgumentHandle<'a, 'b>],
712 _ctx: &dyn FunctionContext<'b>,
713 ) -> Result<CalcValue<'b>, ExcelError> {
714 let start_serial = coerce_to_serial(&args[0])?;
715 let days = coerce_to_int(&args[1])?;
716
717 let start_date = serial_to_date(start_serial)?;
718
719 // Collect holidays if provided
720 // TODO: Implement holiday array support
721 // Holidays parameter is currently accepted but ignored.
722 let holidays: Vec<NaiveDate> = Vec::new();
723
724 let mut current = start_date;
725 let mut remaining = days.abs();
726 let direction: i64 = if days >= 0 { 1 } else { -1 };
727
728 while remaining > 0 {
729 current = if direction > 0 {
730 current.succ_opt().ok_or_else(ExcelError::new_num)?
731 } else {
732 current.pred_opt().ok_or_else(ExcelError::new_num)?
733 };
734
735 if !is_weekend(¤t) && !holidays.contains(¤t) {
736 remaining -= 1;
737 }
738 }
739
740 Ok(CalcValue::Scalar(LiteralValue::Number(date_to_serial(
741 ¤t,
742 ))))
743 }
744}
745
746pub fn register_builtins() {
747 use std::sync::Arc;
748 crate::function_registry::register_function(Arc::new(WeekdayFn));
749 crate::function_registry::register_function(Arc::new(WeeknumFn));
750 crate::function_registry::register_function(Arc::new(DatedifFn));
751 crate::function_registry::register_function(Arc::new(NetworkdaysFn));
752 crate::function_registry::register_function(Arc::new(WorkdayFn));
753}
754
755#[cfg(test)]
756mod tests {
757 use super::*;
758 use crate::test_workbook::TestWorkbook;
759 use crate::traits::ArgumentHandle;
760 use formualizer_parse::parser::{ASTNode, ASTNodeType};
761
762 fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
763 wb.interpreter()
764 }
765 fn lit(v: LiteralValue) -> ASTNode {
766 ASTNode::new(ASTNodeType::Literal(v), None)
767 }
768
769 #[test]
770 fn weekday_basic() {
771 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(WeekdayFn));
772 let ctx = interp(&wb);
773 // Jan 1, 2024 is a Monday
774 // Serial for 2024-01-01: date_to_serial gives us the value
775 let serial = date_to_serial(&NaiveDate::from_ymd_opt(2024, 1, 1).unwrap());
776 let n = lit(LiteralValue::Number(serial));
777 let f = ctx.context.get_function("", "WEEKDAY").unwrap();
778 // Default return_type=1: Monday=2
779 assert_eq!(
780 f.dispatch(
781 &[ArgumentHandle::new(&n, &ctx)],
782 &ctx.function_context(None)
783 )
784 .unwrap()
785 .into_literal(),
786 LiteralValue::Int(2)
787 );
788 }
789
790 #[test]
791 fn datedif_years() {
792 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(DatedifFn));
793 let ctx = interp(&wb);
794 let start = date_to_serial(&NaiveDate::from_ymd_opt(2020, 1, 1).unwrap());
795 let end = date_to_serial(&NaiveDate::from_ymd_opt(2024, 1, 1).unwrap());
796 let s = lit(LiteralValue::Number(start));
797 let e = lit(LiteralValue::Number(end));
798 let unit = lit(LiteralValue::Text("Y".to_string()));
799 let f = ctx.context.get_function("", "DATEDIF").unwrap();
800 assert_eq!(
801 f.dispatch(
802 &[
803 ArgumentHandle::new(&s, &ctx),
804 ArgumentHandle::new(&e, &ctx),
805 ArgumentHandle::new(&unit, &ctx)
806 ],
807 &ctx.function_context(None)
808 )
809 .unwrap()
810 .into_literal(),
811 LiteralValue::Int(4)
812 );
813 }
814}