1use chrono::{Datelike, Duration, Local, NaiveDate, NaiveDateTime, Timelike};
6
7use crate::cell::{date_to_serial, datetime_to_serial, CellValue};
8use crate::error::Result;
9use crate::formula::ast::Expr;
10use crate::formula::eval::{coerce_to_number, coerce_to_string, Evaluator};
11use crate::formula::functions::conversion::{checked_trunc_i32, checked_trunc_i64};
12use crate::formula::functions::{check_arg_count, collect_criteria_range_values};
13
14const MAX_DATE_SERIAL_EXCLUSIVE: f64 = 2_958_466.0;
16const MAX_EXCEL_YEAR: i32 = 9999;
18
19fn num_error() -> CellValue {
20 CellValue::Error("#NUM!".to_string())
21}
22
23fn serial_to_date(serial: f64) -> Option<NaiveDate> {
26 if !serial.is_finite() || serial >= MAX_DATE_SERIAL_EXCLUSIVE {
27 return None;
28 }
29 crate::cell::serial_to_date(serial)
30}
31
32fn serial_to_datetime(serial: f64) -> Option<NaiveDateTime> {
35 if !serial.is_finite() || serial >= MAX_DATE_SERIAL_EXCLUSIVE {
36 return None;
37 }
38 crate::cell::serial_to_datetime(serial)
39}
40
41fn to_serial(v: &CellValue) -> std::result::Result<f64, CellValue> {
43 match v {
44 CellValue::Number(n) => Ok(*n),
45 CellValue::Date(n) => Ok(*n),
46 CellValue::String(s) => {
47 if let Ok(n) = s.parse::<f64>() {
48 Ok(n)
49 } else if let Some(d) = parse_date_string(s) {
50 Ok(date_to_serial(d))
51 } else {
52 Err(CellValue::Error("#VALUE!".to_string()))
53 }
54 }
55 CellValue::Bool(b) => Ok(if *b { 1.0 } else { 0.0 }),
56 CellValue::Empty => Ok(0.0),
57 _ => Err(CellValue::Error("#VALUE!".to_string())),
58 }
59}
60
61fn parse_date_string(s: &str) -> Option<NaiveDate> {
63 if let Ok(d) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
64 return Some(d);
65 }
66 if let Ok(d) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
67 return Some(d);
68 }
69 None
70}
71
72fn add_months_to_date(date: NaiveDate, months: i64) -> Option<NaiveDate> {
75 let total_months = i64::from(date.year())
76 .checked_mul(12)?
77 .checked_add(i64::from(date.month0()))?
78 .checked_add(months)?;
79 let new_year = i32::try_from(total_months.div_euclid(12)).ok()?;
80 let new_month = u32::try_from(total_months.rem_euclid(12) + 1).ok()?;
81 let max_day = last_day_of_month(new_year, new_month);
82 let new_day = date.day().min(max_day);
83 NaiveDate::from_ymd_opt(new_year, new_month, new_day)
84}
85
86fn excel_date(date: NaiveDate) -> Option<NaiveDate> {
88 (date.year() <= MAX_EXCEL_YEAR).then_some(date)
89}
90
91fn excel_ymd(serial: f64) -> std::result::Result<(i32, u32, u32), CellValue> {
96 if !(0.0..MAX_DATE_SERIAL_EXCLUSIVE).contains(&serial) {
97 return Err(num_error());
98 }
99 match serial.floor() {
100 0.0 => Ok((1900, 1, 0)),
101 60.0 => Ok((1900, 2, 29)),
102 _ => serial_to_date(serial)
103 .map(|date| (date.year(), date.month(), date.day()))
104 .ok_or_else(num_error),
105 }
106}
107
108fn shift_month(year: i32, month: u32, months: i64) -> Option<(i32, u32)> {
111 let total = i64::from(year)
112 .checked_mul(12)?
113 .checked_add(i64::from(month) - 1)?
114 .checked_add(months)?;
115 let new_year = i32::try_from(total.div_euclid(12)).ok()?;
116 if !(1900..=MAX_EXCEL_YEAR).contains(&new_year) {
117 return None;
118 }
119 let new_month = u32::try_from(total.rem_euclid(12) + 1).ok()?;
120 Some((new_year, new_month))
121}
122
123fn month_day_serial(year: i32, month: u32, day: u32) -> Option<f64> {
125 let first = NaiveDate::from_ymd_opt(year, month, 1)?;
126 if day == 0 {
127 return Some(date_to_serial(first) - 1.0);
128 }
129 NaiveDate::from_ymd_opt(year, month, day).map(date_to_serial)
130}
131
132fn last_day_of_month(year: i32, month: u32) -> u32 {
134 match month {
135 1 | 3 | 5 | 7 | 8 | 10 | 12 => 31,
136 4 | 6 | 9 | 11 => 30,
137 2 => {
138 if (year % 4 == 0 && year % 100 != 0) || year % 400 == 0 {
139 29
140 } else {
141 28
142 }
143 }
144 _ => 30,
145 }
146}
147
148fn workday_date(value: &CellValue) -> std::result::Result<NaiveDate, CellValue> {
149 if let CellValue::Error(error) = value {
150 return Err(CellValue::Error(error.clone()));
151 }
152 let serial = to_serial(value)?;
153 serial_to_date(serial).ok_or_else(|| CellValue::Error("#VALUE!".to_string()))
154}
155
156fn holiday_dates(
161 args: &[Expr],
162 ctx: &mut Evaluator,
163) -> Result<std::result::Result<Vec<NaiveDate>, CellValue>> {
164 if args.len() < 3 {
165 return Ok(Ok(Vec::new()));
166 }
167
168 let values = collect_criteria_range_values(&args[2], ctx)?;
169 ctx.charge_evaluation_work(u64::try_from(values.len()).unwrap_or(u64::MAX))?;
170 let mut holidays = Vec::new();
171 for value in values {
172 if value == CellValue::Empty {
173 continue;
174 }
175 let date = match workday_date(&value) {
176 Ok(date) => date,
177 Err(error) => return Ok(Err(error)),
178 };
179 if date.weekday().num_days_from_monday() < 5 {
180 holidays.push(date);
181 }
182 }
183 holidays.sort_unstable();
184 holidays.dedup();
185 Ok(Ok(holidays))
186}
187
188fn weekday_count(start: NaiveDate, end: NaiveDate) -> i64 {
189 let days = (end - start).num_days() + 1;
190 let full_weeks = days / 7;
191 let remaining_days = days % 7;
192 let mut count = full_weeks * 5;
193 let start_weekday = start.weekday().num_days_from_monday() as i64;
194 for offset in 0..remaining_days {
195 if (start_weekday + offset) % 7 < 5 {
196 count += 1;
197 }
198 }
199 count
200}
201
202fn checked_add_days(date: NaiveDate, days: i64) -> Option<NaiveDate> {
203 date.checked_add_signed(Duration::try_days(days)?)
204}
205
206fn workday_serial(value: &CellValue) -> std::result::Result<i64, CellValue> {
211 if let CellValue::Error(error) = value {
212 return Err(CellValue::Error(error.clone()));
213 }
214 let serial = to_serial(value)?;
215 if !(0.0..MAX_DATE_SERIAL_EXCLUSIVE).contains(&serial) {
216 return Err(num_error());
217 }
218 Ok(serial.floor() as i64)
219}
220
221fn is_serial_weekday(serial: i64) -> bool {
224 serial.rem_euclid(7) >= 2
225}
226
227fn add_serial_weekdays(mut serial: i64, mut days: u64, direction: i64) -> Option<i64> {
228 while days > 0 && !is_serial_weekday(serial) {
229 serial = serial.checked_add(direction)?;
230 if is_serial_weekday(serial) {
231 days -= 1;
232 }
233 }
234 if days == 0 {
235 return Some(serial);
236 }
237
238 let weeks = days / 5;
239 if weeks > 0 {
240 let calendar_days = i64::try_from(weeks).ok()?.checked_mul(7)?;
241 serial = serial.checked_add(direction.checked_mul(calendar_days)?)?;
242 days %= 5;
243 }
244 while days > 0 {
245 serial = serial.checked_add(direction)?;
246 if is_serial_weekday(serial) {
247 days -= 1;
248 }
249 }
250 Some(serial)
251}
252
253fn holiday_serials(
258 args: &[Expr],
259 ctx: &mut Evaluator,
260) -> Result<std::result::Result<Vec<i64>, CellValue>> {
261 if args.len() < 3 {
262 return Ok(Ok(Vec::new()));
263 }
264 let values = collect_criteria_range_values(&args[2], ctx)?;
265 ctx.charge_evaluation_work(u64::try_from(values.len()).unwrap_or(u64::MAX))?;
266 let mut holidays = Vec::new();
267 for value in values {
268 if value == CellValue::Empty {
269 continue;
270 }
271 match workday_serial(&value) {
272 Ok(serial) if is_serial_weekday(serial) => holidays.push(serial),
273 Ok(_) => {}
274 Err(error) => return Ok(Err(error)),
275 }
276 }
277 holidays.sort_unstable();
278 holidays.dedup();
279 Ok(Ok(holidays))
280}
281
282pub fn fn_date(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
289 check_arg_count("DATE", args, 3, 3)?;
290 let year = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
291 let month = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
292 let day = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
293 let (Some(year), Some(month), Some(day)) = (
294 checked_trunc_i32(year),
295 checked_trunc_i32(month),
296 checked_trunc_i32(day),
297 ) else {
298 return Ok(num_error());
299 };
300 if !(0..=MAX_EXCEL_YEAR).contains(&year) {
301 return Ok(num_error());
302 }
303 let adj_year = if year < 1900 { year + 1900 } else { year };
304
305 let result = NaiveDate::from_ymd_opt(adj_year, 1, 1)
306 .and_then(|base| add_months_to_date(base, i64::from(month) - 1))
307 .and_then(|with_months| checked_add_days(with_months, i64::from(day) - 1))
308 .and_then(excel_date);
309 match result {
310 Some(date) => Ok(CellValue::Date(date_to_serial(date))),
311 None => Ok(num_error()),
312 }
313}
314
315pub fn fn_today(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
317 check_arg_count("TODAY", args, 0, 0)?;
318 let today = Local::now().date_naive();
319 Ok(CellValue::Date(date_to_serial(today)))
320}
321
322pub fn fn_now(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
324 check_arg_count("NOW", args, 0, 0)?;
325 let now = Local::now().naive_local();
326 Ok(CellValue::Date(datetime_to_serial(now)))
327}
328
329pub fn fn_year(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
331 check_arg_count("YEAR", args, 1, 1)?;
332 let v = ctx.eval_expr(&args[0])?;
333 match to_serial(&v) {
334 Ok(serial) => match serial_to_date(serial) {
335 Some(d) => Ok(CellValue::Number(d.year() as f64)),
336 None => Ok(CellValue::Error("#VALUE!".to_string())),
337 },
338 Err(e) => Ok(e),
339 }
340}
341
342pub fn fn_month(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
344 check_arg_count("MONTH", args, 1, 1)?;
345 let v = ctx.eval_expr(&args[0])?;
346 match to_serial(&v) {
347 Ok(serial) => match serial_to_date(serial) {
348 Some(d) => Ok(CellValue::Number(d.month() as f64)),
349 None => Ok(CellValue::Error("#VALUE!".to_string())),
350 },
351 Err(e) => Ok(e),
352 }
353}
354
355pub fn fn_day(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
357 check_arg_count("DAY", args, 1, 1)?;
358 let v = ctx.eval_expr(&args[0])?;
359 match to_serial(&v) {
360 Ok(serial) => match serial_to_date(serial) {
361 Some(d) => Ok(CellValue::Number(d.day() as f64)),
362 None => Ok(CellValue::Error("#VALUE!".to_string())),
363 },
364 Err(e) => Ok(e),
365 }
366}
367
368pub fn fn_hour(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
370 check_arg_count("HOUR", args, 1, 1)?;
371 let v = ctx.eval_expr(&args[0])?;
372 match to_serial(&v) {
373 Ok(serial) => match serial_to_datetime(serial) {
374 Some(dt) => Ok(CellValue::Number(dt.hour() as f64)),
375 None => Ok(CellValue::Error("#VALUE!".to_string())),
376 },
377 Err(e) => Ok(e),
378 }
379}
380
381pub fn fn_minute(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
383 check_arg_count("MINUTE", args, 1, 1)?;
384 let v = ctx.eval_expr(&args[0])?;
385 match to_serial(&v) {
386 Ok(serial) => match serial_to_datetime(serial) {
387 Some(dt) => Ok(CellValue::Number(dt.minute() as f64)),
388 None => Ok(CellValue::Error("#VALUE!".to_string())),
389 },
390 Err(e) => Ok(e),
391 }
392}
393
394pub fn fn_second(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
396 check_arg_count("SECOND", args, 1, 1)?;
397 let v = ctx.eval_expr(&args[0])?;
398 match to_serial(&v) {
399 Ok(serial) => match serial_to_datetime(serial) {
400 Some(dt) => Ok(CellValue::Number(dt.second() as f64)),
401 None => Ok(CellValue::Error("#VALUE!".to_string())),
402 },
403 Err(e) => Ok(e),
404 }
405}
406
407pub fn fn_datedif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
409 check_arg_count("DATEDIF", args, 3, 3)?;
410 let v1 = ctx.eval_expr(&args[0])?;
411 let v2 = ctx.eval_expr(&args[1])?;
412 let unit = coerce_to_string(&ctx.eval_expr(&args[2])?).to_ascii_uppercase();
413
414 let s1 = match to_serial(&v1) {
415 Ok(n) => n,
416 Err(e) => return Ok(e),
417 };
418 let s2 = match to_serial(&v2) {
419 Ok(n) => n,
420 Err(e) => return Ok(e),
421 };
422 if s1 > s2 {
423 return Ok(CellValue::Error("#NUM!".to_string()));
424 }
425
426 let d1 = match serial_to_date(s1) {
427 Some(d) => d,
428 None => return Ok(CellValue::Error("#VALUE!".to_string())),
429 };
430 let d2 = match serial_to_date(s2) {
431 Some(d) => d,
432 None => return Ok(CellValue::Error("#VALUE!".to_string())),
433 };
434
435 let result = match unit.as_str() {
436 "Y" => {
437 let mut years = d2.year() - d1.year();
438 if (d2.month(), d2.day()) < (d1.month(), d1.day()) {
439 years -= 1;
440 }
441 years as f64
442 }
443 "M" => {
444 let mut months = (d2.year() - d1.year()) * 12 + d2.month() as i32 - d1.month() as i32;
445 if d2.day() < d1.day() {
446 months -= 1;
447 }
448 months as f64
449 }
450 "D" => s2.floor() - s1.floor(),
451 "YM" => {
452 let mut months = d2.month() as i32 - d1.month() as i32;
453 if d2.day() < d1.day() {
454 months -= 1;
455 }
456 if months < 0 {
457 months += 12;
458 }
459 months as f64
460 }
461 "YD" => {
462 let mut d1_this_year = NaiveDate::from_ymd_opt(
463 d2.year(),
464 d1.month(),
465 d1.day().min(last_day_of_month(d2.year(), d1.month())),
466 );
467 let d1_this_year = match d1_this_year.take() {
468 Some(d) => d,
469 None => return Ok(CellValue::Error("#VALUE!".to_string())),
470 };
471 let days = if d2 >= d1_this_year {
472 (d2 - d1_this_year).num_days()
473 } else {
474 let Some(d1_last_year) = NaiveDate::from_ymd_opt(
475 d2.year() - 1,
476 d1.month(),
477 d1.day().min(last_day_of_month(d2.year() - 1, d1.month())),
478 ) else {
479 return Ok(CellValue::Error("#VALUE!".to_string()));
480 };
481 (d2 - d1_last_year).num_days()
482 };
483 days as f64
484 }
485 "MD" => {
486 let mut days = d2.day() as i32 - d1.day() as i32;
487 if days < 0 {
488 let prev_month_end = if d2.month() == 1 {
489 last_day_of_month(d2.year() - 1, 12)
490 } else {
491 last_day_of_month(d2.year(), d2.month() - 1)
492 };
493 days += prev_month_end as i32;
494 }
495 days as f64
496 }
497 _ => return Ok(CellValue::Error("#NUM!".to_string())),
498 };
499 Ok(CellValue::Number(result))
500}
501
502pub fn fn_edate(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
508 check_arg_count("EDATE", args, 2, 2)?;
509 let v = ctx.eval_expr(&args[0])?;
510 let months = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
511 let Some(months) = checked_trunc_i32(months) else {
512 return Ok(num_error());
513 };
514 let serial = match to_serial(&v) {
515 Ok(n) => n,
516 Err(e) => return Ok(e),
517 };
518 let (year, month, day) = match excel_ymd(serial) {
519 Ok(ymd) => ymd,
520 Err(e) => return Ok(e),
521 };
522 let result = shift_month(year, month, i64::from(months)).and_then(|(year, month)| {
523 month_day_serial(year, month, day.min(last_day_of_month(year, month)))
524 });
525 match result {
526 Some(serial) => Ok(CellValue::Date(serial)),
527 None => Ok(num_error()),
528 }
529}
530
531pub fn fn_eomonth(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
536 check_arg_count("EOMONTH", args, 2, 2)?;
537 let v = ctx.eval_expr(&args[0])?;
538 let months = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
539 let Some(months) = checked_trunc_i32(months) else {
540 return Ok(num_error());
541 };
542 let serial = match to_serial(&v) {
543 Ok(n) => n,
544 Err(e) => return Ok(e),
545 };
546 let (year, month, _) = match excel_ymd(serial) {
547 Ok(ymd) => ymd,
548 Err(e) => return Ok(e),
549 };
550 let result = shift_month(year, month, i64::from(months))
551 .and_then(|(year, month)| month_day_serial(year, month, last_day_of_month(year, month)));
552 match result {
553 Some(serial) => Ok(CellValue::Date(serial)),
554 None => Ok(num_error()),
555 }
556}
557
558pub fn fn_datevalue(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
560 check_arg_count("DATEVALUE", args, 1, 1)?;
561 let text = coerce_to_string(&ctx.eval_expr(&args[0])?);
562 match parse_date_string(&text) {
563 Some(d) => Ok(CellValue::Date(date_to_serial(d))),
564 None => Ok(CellValue::Error("#VALUE!".to_string())),
565 }
566}
567
568pub fn fn_weekday(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
570 check_arg_count("WEEKDAY", args, 1, 2)?;
571 let v = ctx.eval_expr(&args[0])?;
572 let return_type = if args.len() > 1 {
573 let value = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
574 let Some(return_type) = checked_trunc_i32(value) else {
575 return Ok(num_error());
576 };
577 return_type
578 } else {
579 1
580 };
581 let serial = match to_serial(&v) {
582 Ok(n) => n,
583 Err(e) => return Ok(e),
584 };
585 let date = match serial_to_date(serial) {
586 Some(d) => d,
587 None => return Ok(CellValue::Error("#VALUE!".to_string())),
588 };
589 let weekday_num = date.weekday().num_days_from_monday(); let result = match return_type {
592 1 => ((weekday_num + 1) % 7) + 1, 2 => weekday_num + 1, 3 => weekday_num, _ => return Ok(CellValue::Error("#NUM!".to_string())),
596 };
597 Ok(CellValue::Number(result as f64))
598}
599
600pub fn fn_weeknum(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
602 check_arg_count("WEEKNUM", args, 1, 2)?;
603 let v = ctx.eval_expr(&args[0])?;
604 let return_type = if args.len() > 1 {
605 let value = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
606 let Some(return_type) = checked_trunc_i32(value) else {
607 return Ok(num_error());
608 };
609 return_type
610 } else {
611 1
612 };
613 let serial = match to_serial(&v) {
614 Ok(n) => n,
615 Err(e) => return Ok(e),
616 };
617 let date = match serial_to_date(serial) {
618 Some(d) => d,
619 None => return Ok(CellValue::Error("#VALUE!".to_string())),
620 };
621 let Some(jan1) = NaiveDate::from_ymd_opt(date.year(), 1, 1) else {
622 return Ok(CellValue::Error("#VALUE!".to_string()));
623 };
624 let jan1_weekday = jan1.weekday().num_days_from_monday(); let day_of_year = date.ordinal() as i32;
626
627 let week_start_offset = match return_type {
628 1 => (jan1_weekday + 1) % 7, 2 => jan1_weekday, _ => return Ok(CellValue::Error("#NUM!".to_string())),
631 };
632 let week = (day_of_year - 1 + week_start_offset as i32) / 7 + 1;
633 Ok(CellValue::Number(week as f64))
634}
635
636pub fn fn_networkdays(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
638 check_arg_count("NETWORKDAYS", args, 2, 3)?;
639 let d1 = match workday_date(&ctx.eval_expr(&args[0])?) {
640 Ok(date) => date,
641 Err(error) => return Ok(error),
642 };
643 let d2 = match workday_date(&ctx.eval_expr(&args[1])?) {
644 Ok(date) => date,
645 Err(error) => return Ok(error),
646 };
647 let holidays = match holiday_dates(args, ctx)? {
648 Ok(holidays) => holidays,
649 Err(error) => return Ok(error),
650 };
651
652 let (start, end, sign) = if d1 <= d2 {
653 (d1, d2, 1i32)
654 } else {
655 (d2, d1, -1i32)
656 };
657
658 let first = holidays.partition_point(|date| *date < start);
659 let past_end = holidays.partition_point(|date| *date <= end);
660 let holiday_count = i64::try_from(past_end - first).unwrap_or(i64::MAX);
661 let count = weekday_count(start, end).saturating_sub(holiday_count);
662 Ok(CellValue::Number((count * i64::from(sign)) as f64))
663}
664
665pub fn fn_workday(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
674 check_arg_count("WORKDAY", args, 2, 3)?;
675 let start = match workday_serial(&ctx.eval_expr(&args[0])?) {
676 Ok(serial) => serial,
677 Err(error) => return Ok(error),
678 };
679 let days = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
680 let Some(days) = checked_trunc_i64(days, i64::MIN, i64::MAX) else {
681 return Ok(num_error());
682 };
683 let holidays = match holiday_serials(args, ctx)? {
684 Ok(holidays) => holidays,
685 Err(error) => return Ok(error),
686 };
687 if days == 0 {
688 return Ok(CellValue::Date(start as f64));
689 }
690 let direction = if days >= 0 { 1 } else { -1 };
691 let Some(mut current) = add_serial_weekdays(start, days.unsigned_abs(), direction) else {
692 return Ok(num_error());
693 };
694 let pending: Box<dyn Iterator<Item = &i64>> = if direction > 0 {
698 let first = holidays.partition_point(|serial| *serial <= start);
699 ctx.charge_evaluation_work(u64::try_from(holidays.len() - first).unwrap_or(u64::MAX))?;
700 Box::new(holidays[first..].iter())
701 } else {
702 let past_last = holidays.partition_point(|serial| *serial < start);
703 ctx.charge_evaluation_work(u64::try_from(past_last).unwrap_or(u64::MAX))?;
704 Box::new(holidays[..past_last].iter().rev())
705 };
706 for holiday in pending {
707 let reached = if direction > 0 {
708 *holiday <= current
709 } else {
710 *holiday >= current
711 };
712 if !reached {
713 break;
714 }
715 let Some(next) = add_serial_weekdays(current, 1, direction) else {
716 return Ok(num_error());
717 };
718 current = next;
719 }
720 if (1..MAX_DATE_SERIAL_EXCLUSIVE as i64).contains(¤t) {
721 Ok(CellValue::Date(current as f64))
722 } else {
723 Ok(num_error())
724 }
725}
726
727#[cfg(test)]
728mod tests {
729 use super::*;
730 use crate::cell::date_to_serial;
731 use crate::formula::eval::{evaluate, CellSnapshot};
732 use crate::formula::parser::parse_formula;
733
734 fn eval(formula: &str) -> CellValue {
735 let snap = CellSnapshot::new("Sheet1".to_string());
736 let expr = parse_formula(formula).unwrap();
737 evaluate(&expr, &snap).unwrap()
738 }
739
740 fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
741 let mut snap = CellSnapshot::new("Sheet1".to_string());
742 for (sheet, col, row, value) in data {
743 snap.set_cell(sheet, *col, *row, value.clone());
744 }
745 let expr = parse_formula(formula).unwrap();
746 evaluate(&expr, &snap).unwrap()
747 }
748
749 #[test]
750 fn test_date_basic() {
751 let result = eval("DATE(2024,1,15)");
752 let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 15).unwrap());
753 assert_eq!(result, CellValue::Date(expected));
754 }
755
756 #[test]
757 fn test_date_month_overflow() {
758 let result = eval("DATE(2024,13,1)");
759 let expected = date_to_serial(NaiveDate::from_ymd_opt(2025, 1, 1).unwrap());
760 assert_eq!(result, CellValue::Date(expected));
761 }
762
763 #[test]
764 fn test_year() {
765 assert_eq!(eval("YEAR(DATE(2024,6,15))"), CellValue::Number(2024.0));
766 }
767
768 #[test]
769 fn test_month() {
770 assert_eq!(eval("MONTH(DATE(2024,6,15))"), CellValue::Number(6.0));
771 }
772
773 #[test]
774 fn test_day() {
775 assert_eq!(eval("DAY(DATE(2024,6,15))"), CellValue::Number(15.0));
776 }
777
778 #[test]
779 fn test_hour() {
780 assert_eq!(eval("HOUR(36526.5)"), CellValue::Number(12.0));
782 }
783
784 #[test]
785 fn test_minute() {
786 let serial = 36526.0 + 52200.0 / 86400.0;
789 let formula = format!("MINUTE({serial})");
790 assert_eq!(eval(&formula), CellValue::Number(30.0));
791 }
792
793 #[test]
794 fn test_second() {
795 let serial = 36526.0 + (14.0 * 3600.0 + 30.0 * 60.0 + 45.0) / 86400.0;
797 let formula = format!("SECOND({serial})");
798 assert_eq!(eval(&formula), CellValue::Number(45.0));
799 }
800
801 #[test]
802 fn test_datedif_years() {
803 assert_eq!(
804 eval("DATEDIF(DATE(2020,1,1),DATE(2024,6,15),\"Y\")"),
805 CellValue::Number(4.0)
806 );
807 }
808
809 #[test]
810 fn test_datedif_months() {
811 assert_eq!(
812 eval("DATEDIF(DATE(2024,1,1),DATE(2024,6,15),\"M\")"),
813 CellValue::Number(5.0)
814 );
815 }
816
817 #[test]
818 fn test_datedif_days() {
819 assert_eq!(
820 eval("DATEDIF(DATE(2024,1,1),DATE(2024,1,31),\"D\")"),
821 CellValue::Number(30.0)
822 );
823 }
824
825 #[test]
826 fn test_edate() {
827 let result = eval("EDATE(DATE(2024,1,31),1)");
828 let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 2, 29).unwrap());
829 assert_eq!(result, CellValue::Date(expected));
830 }
831
832 #[test]
833 fn test_eomonth() {
834 let result = eval("EOMONTH(DATE(2024,1,15),1)");
835 let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 2, 29).unwrap());
836 assert_eq!(result, CellValue::Date(expected));
837 }
838
839 #[test]
840 fn test_datevalue() {
841 let result = eval(r#"DATEVALUE("2024-06-15")"#);
842 let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
843 assert_eq!(result, CellValue::Date(expected));
844 }
845
846 #[test]
847 fn test_weekday_type1() {
848 assert_eq!(eval("WEEKDAY(DATE(2024,1,15),1)"), CellValue::Number(2.0));
850 }
851
852 #[test]
853 fn test_weekday_type2() {
854 assert_eq!(eval("WEEKDAY(DATE(2024,1,15),2)"), CellValue::Number(1.0));
856 }
857
858 #[test]
859 fn test_weeknum() {
860 assert_eq!(eval("WEEKNUM(DATE(2024,1,15),1)"), CellValue::Number(3.0));
862 }
863
864 #[test]
865 fn test_networkdays() {
866 assert_eq!(
868 eval("NETWORKDAYS(DATE(2024,1,1),DATE(2024,1,5))"),
869 CellValue::Number(5.0)
870 );
871 }
872
873 #[test]
874 fn test_workday() {
875 let result = eval("WORKDAY(DATE(2024,1,5),1)");
877 let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 8).unwrap());
878 assert_eq!(result, CellValue::Date(expected));
879 }
880
881 #[test]
882 fn networkdays_excludes_range_holidays_once_and_preserves_reverse_sign() {
883 let holidays = [
884 (
885 "Sheet1",
886 1,
887 1,
888 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 1).unwrap())),
889 ),
890 (
891 "Sheet1",
892 1,
893 2,
894 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 1).unwrap())),
895 ),
896 (
897 "Sheet1",
898 1,
899 3,
900 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 6).unwrap())),
901 ),
902 ];
903 assert_eq!(
904 eval_with_data(
905 "NETWORKDAYS(DATE(2024,1,1),DATE(2024,1,7),A1:A3)",
906 &holidays
907 ),
908 CellValue::Number(4.0)
909 );
910 assert_eq!(
911 eval_with_data(
912 "NETWORKDAYS(DATE(2024,1,7),DATE(2024,1,1),A1:A3)",
913 &holidays
914 ),
915 CellValue::Number(-4.0)
916 );
917 }
918
919 #[test]
920 fn workday_skips_scalar_and_range_holidays_in_both_directions() {
921 assert_eq!(
922 eval("WORKDAY(DATE(2024,1,5),1,DATE(2024,1,8))"),
923 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 9).unwrap()))
924 );
925 let holidays = [
926 (
927 "Sheet1",
928 1,
929 1,
930 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 8).unwrap())),
931 ),
932 (
933 "Sheet1",
934 1,
935 2,
936 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 8).unwrap())),
937 ),
938 (
939 "Sheet1",
940 1,
941 3,
942 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 7).unwrap())),
943 ),
944 ];
945 assert_eq!(
946 eval_with_data("WORKDAY(DATE(2024,1,9),-1,A1:A3)", &holidays),
947 CellValue::Date(date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 5).unwrap()))
948 );
949 }
950
951 #[test]
952 fn workday_functions_propagate_holiday_errors_and_reject_invalid_dates() {
953 let errors = [("Sheet1", 1, 1, CellValue::Error("#N/A".to_string()))];
954 assert_eq!(
955 eval_with_data("NETWORKDAYS(DATE(2024,1,1),DATE(2024,1,5),A1)", &errors),
956 CellValue::Error("#N/A".to_string())
957 );
958 assert_eq!(
959 eval_with_data("WORKDAY(DATE(2024,1,5),1,A1)", &errors),
960 CellValue::Error("#N/A".to_string())
961 );
962 assert_eq!(
963 eval("NETWORKDAYS(0,DATE(2024,1,5))"),
964 CellValue::Error("#VALUE!".to_string())
965 );
966 assert_eq!(eval("WORKDAY(0,1)"), CellValue::Date(2.0));
967 assert_eq!(eval("WORKDAY(-1,1)"), CellValue::Error("#NUM!".to_string()));
968 }
969}