Skip to main content

sheetkit_core/formula/functions/
date_time.rs

1//! Date/time formula functions: DATE, TODAY, NOW, YEAR, MONTH, DAY, HOUR,
2//! MINUTE, SECOND, DATEDIF, EDATE, EOMONTH, DATEVALUE, WEEKDAY, WEEKNUM,
3//! NETWORKDAYS, WORKDAY.
4
5use 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
14/// One past the largest Excel date serial (9999-12-31 23:59:59).
15const MAX_DATE_SERIAL_EXCLUSIVE: f64 = 2_958_466.0;
16/// Largest year representable in an Excel date.
17const MAX_EXCEL_YEAR: i32 = 9999;
18
19fn num_error() -> CellValue {
20    CellValue::Error("#NUM!".to_string())
21}
22
23/// Convert a serial to a date, rejecting non-finite or post-9999 serials
24/// before they reach chrono arithmetic.
25fn 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
32/// Convert a serial to a date-time with the same domain check as
33/// [`serial_to_date`].
34fn 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
41/// Convert a CellValue to an Excel serial number.
42fn 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
61/// Parse common date string formats: YYYY-MM-DD and MM/DD/YYYY.
62fn 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
72/// Add months to a date, clamping to end of month if needed. Returns `None`
73/// when the result falls outside the chrono date range.
74fn 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
86/// Return `date` when its year is within the Excel date range.
87fn excel_date(date: NaiveDate) -> Option<NaiveDate> {
88    (date.year() <= MAX_EXCEL_YEAR).then_some(date)
89}
90
91/// Year, month, and day of a serial on Excel's calendar, where serial 0 is
92/// 1900-01-00 and serial 60 is 1900-02-29.
93///
94/// Negative, non-finite, and post-9999 serials return `#NUM!`.
95fn 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
108/// Shift a year and month by `months`, rejecting results outside the Excel
109/// years 1900..=9999.
110fn 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
123/// Serial of `day` in the given month; day 0 is the day before the first.
124fn 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
132/// Return the last day of the given month.
133fn 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
156/// Collect weekday holidays as a sorted, deduplicated list.
157///
158/// Evaluation work proportional to the number of holiday values is charged
159/// before they are converted and sorted.
160fn 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
206/// Integer serial day for WORKDAY on Excel's serial calendar.
207///
208/// Error values propagate, unconvertible text returns `#VALUE!`, and
209/// negative, non-finite, or post-9999 serials return `#NUM!`.
210fn 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
221/// Whether a serial day is Monday..Friday. Excel's serial calendar is
222/// uniform, so serial 0 is a Saturday and serial 1 a Sunday.
223fn 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
253/// Collect WORKDAY holidays as sorted, deduplicated weekday serials.
254///
255/// Evaluation work proportional to the number of holiday values is charged
256/// before they are converted and sorted.
257fn 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
282/// DATE(year, month, day) - constructs a date serial number.
283///
284/// Arguments are truncated toward zero. Years below 1900 are offset by 1900;
285/// month and day overflow roll into later or earlier periods. Non-finite
286/// arguments, arguments outside the `i32` range, a year outside 0..=9999, or
287/// a result after 9999-12-31 return `#NUM!`.
288pub 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
315/// TODAY() - returns today's date serial number.
316pub 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
322/// NOW() - returns current date and time serial number.
323pub 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
329/// YEAR(serial_number) - extracts the year from a date serial.
330pub 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
342/// MONTH(serial_number) - extracts the month from a date serial.
343pub 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
355/// DAY(serial_number) - extracts the day from a date serial.
356pub 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
368/// HOUR(serial_number) - extracts the hour from a datetime serial.
369pub 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
381/// MINUTE(serial_number) - extracts the minute from a datetime serial.
382pub 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
394/// SECOND(serial_number) - extracts the second from a datetime serial.
395pub 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
407/// DATEDIF(start_date, end_date, unit) - difference between two dates.
408pub 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
502/// EDATE(start_date, months) - date that is N months before/after start_date.
503///
504/// `months` is truncated toward zero. Serial 0 is 1900-01-00, as in Excel.
505/// A negative or post-9999 start, a non-finite month count, one outside the
506/// `i32` range, or a result month outside 1900-01..9999-12 returns `#NUM!`.
507pub 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
531/// EOMONTH(start_date, months) - last day of the month N months away.
532///
533/// Uses the same start and month-count domains as EDATE and returns `#NUM!`
534/// outside them.
535pub 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
558/// DATEVALUE(date_text) - converts a date string to a serial number.
559pub 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
568/// WEEKDAY(serial_number, \[return_type\]) - day of the week.
569pub 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    // chrono: Mon=1 .. Sun=7
590    let weekday_num = date.weekday().num_days_from_monday(); // Mon=0 .. Sun=6
591    let result = match return_type {
592        1 => ((weekday_num + 1) % 7) + 1, // Sun=1, Mon=2, ..., Sat=7
593        2 => weekday_num + 1,             // Mon=1, ..., Sun=7
594        3 => weekday_num,                 // Mon=0, ..., Sun=6
595        _ => return Ok(CellValue::Error("#NUM!".to_string())),
596    };
597    Ok(CellValue::Number(result as f64))
598}
599
600/// WEEKNUM(serial_number, \[return_type\]) - week number of the year.
601pub 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(); // Mon=0..Sun=6
625    let day_of_year = date.ordinal() as i32;
626
627    let week_start_offset = match return_type {
628        1 => (jan1_weekday + 1) % 7, // Sunday-based
629        2 => jan1_weekday,           // Monday-based
630        _ => 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
636/// NETWORKDAYS(start_date, end_date, \[holidays\]) - working days between two dates.
637pub 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
665/// WORKDAY(start_date, days, \[holidays\]) - date after N working days.
666///
667/// Weekdays follow Excel's serial calendar, where serial 0 is a Saturday and
668/// serial 60 (1900-02-29) is a Wednesday. `days` is truncated toward zero;
669/// a negative or post-9999 start or holiday, a non-finite day count, or a
670/// result outside the Excel date range returns `#NUM!`. Holidays are sorted
671/// once and walked with a monotone cursor, and the walk is charged as
672/// evaluation work before it runs.
673pub 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    // Each holiday between start and the running result pushes the result
695    // one working day further. Holidays are distinct weekdays, so a single
696    // ordered pass is exact.
697    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(&current) {
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        // Serial 36526.5 = 2000-01-01 12:00:00
781        assert_eq!(eval("HOUR(36526.5)"), CellValue::Number(12.0));
782    }
783
784    #[test]
785    fn test_minute() {
786        // 36526 + 14*3600 + 30*60 = time fraction
787        // 14:30 = (14*3600 + 30*60) / 86400 = 52200/86400 = 0.604166...
788        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        // 36526 + 14*3600 + 30*60 + 45 = time fraction
796        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        // 2024-01-15 is a Monday
849        assert_eq!(eval("WEEKDAY(DATE(2024,1,15),1)"), CellValue::Number(2.0));
850    }
851
852    #[test]
853    fn test_weekday_type2() {
854        // Monday = 1 in type 2
855        assert_eq!(eval("WEEKDAY(DATE(2024,1,15),2)"), CellValue::Number(1.0));
856    }
857
858    #[test]
859    fn test_weeknum() {
860        // 2024-01-15 is in week 3 (Sunday-based)
861        assert_eq!(eval("WEEKNUM(DATE(2024,1,15),1)"), CellValue::Number(3.0));
862    }
863
864    #[test]
865    fn test_networkdays() {
866        // Jan 1 2024 (Mon) to Jan 5 2024 (Fri) = 5 working days
867        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        // Start on Fri Jan 5 2024, add 1 workday = Mon Jan 8 2024
876        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}