formualizer_eval/builtins/datetime/
date_parts.rs

1//! Date and time component extraction functions
2
3use super::serial::{date_to_serial, datetime_to_serial, serial_to_date, serial_to_datetime};
4use crate::args::ArgSchema;
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, FunctionContext};
7use chrono::{Datelike, NaiveDate, Timelike};
8use formualizer_common::{ExcelError, ExcelErrorKind, 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::Text(s) => s.parse::<f64>().map_err(|_| {
17            ExcelError::new_value().with_message("Date/time serial is not a valid number")
18        }),
19        LiteralValue::Boolean(b) => Ok(if b { 1.0 } else { 0.0 }),
20        LiteralValue::Date(d) => Ok(date_to_serial(&d)),
21        LiteralValue::DateTime(dt) => Ok(datetime_to_serial(&dt)),
22        LiteralValue::Empty => Ok(0.0),
23        LiteralValue::Error(e) => Err(e),
24        _ => Err(ExcelError::new_value()
25            .with_message("Date/time functions expect numeric or text-numeric serials")),
26    }
27}
28
29fn coerce_to_date(arg: &ArgumentHandle) -> Result<NaiveDate, ExcelError> {
30    let serial = coerce_to_serial(arg)?;
31    serial_to_date(serial)
32}
33
34fn days_in_year(year: i32) -> f64 {
35    if NaiveDate::from_ymd_opt(year, 2, 29).is_some() {
36        366.0
37    } else {
38        365.0
39    }
40}
41
42fn is_last_day_of_month(d: NaiveDate) -> bool {
43    d.succ_opt().is_none_or(|next| next.month() != d.month())
44}
45
46fn next_month(year: i32, month: u32) -> (i32, u32) {
47    if month == 12 {
48        (year + 1, 1)
49    } else {
50        (year, month + 1)
51    }
52}
53
54fn days_360_between(start: NaiveDate, end: NaiveDate, european: bool) -> i64 {
55    let sy = start.year();
56    let sm = start.month();
57    let mut sd = start.day();
58
59    let mut ey = end.year();
60    let mut em = end.month();
61    let mut ed = end.day();
62
63    if european {
64        if sd == 31 {
65            sd = 30;
66        }
67        if ed == 31 {
68            ed = 30;
69        }
70    } else {
71        if sd == 31 || is_last_day_of_month(start) {
72            sd = 30;
73        }
74
75        if ed == 31 || is_last_day_of_month(end) {
76            if sd < 30 {
77                let (ny, nm) = next_month(ey, em);
78                ey = ny;
79                em = nm;
80                ed = 1;
81            } else {
82                ed = 30;
83            }
84        }
85    }
86
87    360 * i64::from(ey - sy)
88        + 30 * i64::from(em as i32 - sm as i32)
89        + i64::from(ed as i32 - sd as i32)
90}
91
92/// Returns the number of whole days between two date serial values.
93///
94/// # Remarks
95/// - Result is `end_date - start_date`; negative results are allowed.
96/// - Fractional serial inputs are truncated to their date portion.
97/// - Serials are interpreted with the Excel 1900 mapping (including the 1900 leap-year bug behavior).
98/// - This function currently does not switch interpretation based on workbook `1900`/`1904` mode.
99///
100/// # Examples
101/// ```yaml,sandbox
102/// title: "Positive day difference"
103/// formula: "=DAYS(45366, 45323)"
104/// expected: 43
105/// ```
106///
107/// ```yaml,sandbox
108/// title: "Negative day difference"
109/// formula: "=DAYS(45323, 45366)"
110/// expected: -43
111/// ```
112///
113/// ```yaml,docs
114/// related:
115///   - DATEDIF
116///   - DAYS360
117///   - NETWORKDAYS
118/// faq:
119///   - q: "Does DAYS count partial-day time fractions?"
120///     a: "No. DAYS truncates both serial inputs to whole dates before subtracting end_date - start_date."
121/// ```
122#[derive(Debug)]
123pub struct DaysFn;
124
125/// [formualizer-docgen:schema:start]
126/// Name: DAYS
127/// Type: DaysFn
128/// Min args: 2
129/// Max args: 2
130/// Variadic: false
131/// Signature: DAYS(arg1: number@scalar, arg2: number@scalar)
132/// 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}
133/// Caps: PURE
134/// [formualizer-docgen:schema:end]
135impl Function for DaysFn {
136    func_caps!(PURE);
137
138    fn name(&self) -> &'static str {
139        "DAYS"
140    }
141
142    fn min_args(&self) -> usize {
143        2
144    }
145
146    fn arg_schema(&self) -> &'static [ArgSchema] {
147        use std::sync::LazyLock;
148        static TWO: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
149            vec![
150                ArgSchema::number_lenient_scalar(),
151                ArgSchema::number_lenient_scalar(),
152            ]
153        });
154        &TWO[..]
155    }
156
157    fn eval<'a, 'b, 'c>(
158        &self,
159        args: &'c [ArgumentHandle<'a, 'b>],
160        _ctx: &dyn FunctionContext<'b>,
161    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
162        let end = coerce_to_date(&args[0])?;
163        let start = coerce_to_date(&args[1])?;
164        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
165            (end - start).num_days() as f64,
166        )))
167    }
168}
169
170/// Returns the day count between two dates using a 30/360 convention.
171///
172/// # Remarks
173/// - `method` omitted or `FALSE` uses U.S. (NASD) rules; `TRUE` uses the European 30E/360 method.
174/// - Inputs are coerced to dates by truncating serials to integer days.
175/// - Serials are interpreted with the Excel 1900 date mapping, not a workbook-specific date system.
176///
177/// # Examples
178/// ```yaml,sandbox
179/// title: "U.S. 30/360 method"
180/// formula: "=DAYS360(40574, 40602)"
181/// expected: 30
182/// ```
183///
184/// ```yaml,sandbox
185/// title: "European 30E/360 method"
186/// formula: "=DAYS360(40574, 40602, TRUE)"
187/// expected: 28
188/// ```
189///
190/// ```yaml,docs
191/// related:
192///   - DAYS
193///   - YEARFRAC
194///   - DATEDIF
195/// faq:
196///   - q: "Why does DAYS360 differ from actual day counts?"
197///     a: "DAYS360 applies 30/360 financial conventions, so month-end dates are adjusted by rule instead of using calendar day totals."
198/// ```
199#[derive(Debug)]
200pub struct Days360Fn;
201
202/// [formualizer-docgen:schema:start]
203/// Name: DAYS360
204/// Type: Days360Fn
205/// Min args: 2
206/// Max args: variadic
207/// Variadic: true
208/// Signature: DAYS360(arg1: number@scalar, arg2: number@scalar, arg3...: any@scalar)
209/// 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}
210/// Caps: PURE
211/// [formualizer-docgen:schema:end]
212impl Function for Days360Fn {
213    func_caps!(PURE);
214
215    fn name(&self) -> &'static str {
216        "DAYS360"
217    }
218
219    fn min_args(&self) -> usize {
220        2
221    }
222
223    fn variadic(&self) -> bool {
224        true
225    }
226
227    fn arg_schema(&self) -> &'static [ArgSchema] {
228        use std::sync::LazyLock;
229        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
230            vec![
231                ArgSchema::number_lenient_scalar(),
232                ArgSchema::number_lenient_scalar(),
233                ArgSchema::any(),
234            ]
235        });
236        &SCHEMA[..]
237    }
238
239    fn eval<'a, 'b, 'c>(
240        &self,
241        args: &'c [ArgumentHandle<'a, 'b>],
242        _ctx: &dyn FunctionContext<'b>,
243    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
244        let start = coerce_to_date(&args[0])?;
245        let end = coerce_to_date(&args[1])?;
246
247        let european = if args.len() >= 3 {
248            match args[2].value()?.into_literal() {
249                LiteralValue::Boolean(b) => b,
250                LiteralValue::Number(n) => n != 0.0,
251                LiteralValue::Int(i) => i != 0,
252                LiteralValue::Empty => false,
253                LiteralValue::Error(e) => {
254                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
255                }
256                _ => {
257                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
258                        ExcelError::new(ExcelErrorKind::Value),
259                    )));
260                }
261            }
262        } else {
263            false
264        };
265
266        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
267            days_360_between(start, end, european) as f64,
268        )))
269    }
270}
271
272/// Returns the fraction of a year between two dates for a selected day-count basis.
273///
274/// # Remarks
275/// - Supported `basis` values: `0` (US 30/360), `1` (actual/actual), `2` (actual/360), `3` (actual/365), `4` (European 30/360).
276/// - If `start_date > end_date`, the result is negative.
277/// - Invalid `basis` values return `#NUM!`.
278/// - Serial dates are interpreted with the Excel 1900 mapping rather than workbook `1900`/`1904` context.
279///
280/// # Examples
281/// ```yaml,sandbox
282/// title: "Actual/360 convention"
283/// formula: "=YEARFRAC(44197, 44378, 2)"
284/// expected: 0.5027777778
285/// ```
286///
287/// ```yaml,sandbox
288/// title: "Actual/365 convention"
289/// formula: "=YEARFRAC(44197, 44378, 3)"
290/// expected: 0.4958904110
291/// ```
292///
293/// ```yaml,docs
294/// related:
295///   - DAYS360
296///   - DAYS
297///   - DATEDIF
298/// faq:
299///   - q: "How does the basis argument change YEARFRAC?"
300///     a: "Basis selects the day-count convention (actual or 30/360 variants), so the same dates can yield different fractions."
301/// ```
302#[derive(Debug)]
303pub struct YearFracFn;
304
305/// [formualizer-docgen:schema:start]
306/// Name: YEARFRAC
307/// Type: YearFracFn
308/// Min args: 2
309/// Max args: variadic
310/// Variadic: true
311/// Signature: YEARFRAC(arg1: number@scalar, arg2: number@scalar, arg3...: number@scalar)
312/// 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=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
313/// Caps: PURE
314/// [formualizer-docgen:schema:end]
315impl Function for YearFracFn {
316    func_caps!(PURE);
317
318    fn name(&self) -> &'static str {
319        "YEARFRAC"
320    }
321
322    fn min_args(&self) -> usize {
323        2
324    }
325
326    fn variadic(&self) -> bool {
327        true
328    }
329
330    fn arg_schema(&self) -> &'static [ArgSchema] {
331        use std::sync::LazyLock;
332        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
333            vec![
334                ArgSchema::number_lenient_scalar(),
335                ArgSchema::number_lenient_scalar(),
336                ArgSchema::number_lenient_scalar(),
337            ]
338        });
339        &SCHEMA[..]
340    }
341
342    fn eval<'a, 'b, 'c>(
343        &self,
344        args: &'c [ArgumentHandle<'a, 'b>],
345        _ctx: &dyn FunctionContext<'b>,
346    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
347        let start = coerce_to_date(&args[0])?;
348        let end = coerce_to_date(&args[1])?;
349
350        let basis = if args.len() >= 3 {
351            match args[2].value()?.into_literal() {
352                LiteralValue::Number(n) => n.trunc() as i64,
353                LiteralValue::Int(i) => i,
354                LiteralValue::Boolean(b) => {
355                    if b {
356                        1
357                    } else {
358                        0
359                    }
360                }
361                LiteralValue::Empty => 0,
362                LiteralValue::Error(e) => {
363                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
364                }
365                _ => {
366                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
367                        ExcelError::new(ExcelErrorKind::Value),
368                    )));
369                }
370            }
371        } else {
372            0
373        };
374
375        if !(0..=4).contains(&basis) {
376            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
377                ExcelError::new(ExcelErrorKind::Num),
378            )));
379        }
380
381        if start == end {
382            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(0.0)));
383        }
384
385        let (s, e, sign) = if start <= end {
386            (start, end, 1.0)
387        } else {
388            (end, start, -1.0)
389        };
390
391        let actual_days = (e - s).num_days() as f64;
392        let frac = match basis {
393            0 => days_360_between(s, e, false) as f64 / 360.0,
394            1 => {
395                if s.year() == e.year() {
396                    actual_days / days_in_year(s.year())
397                } else {
398                    let start_year_end = NaiveDate::from_ymd_opt(s.year() + 1, 1, 1).unwrap();
399                    let end_year_start = NaiveDate::from_ymd_opt(e.year(), 1, 1).unwrap();
400
401                    let mut out = (start_year_end - s).num_days() as f64 / days_in_year(s.year());
402                    for year in (s.year() + 1)..e.year() {
403                        out += 1.0;
404                    }
405                    out + (e - end_year_start).num_days() as f64 / days_in_year(e.year())
406                }
407            }
408            2 => actual_days / 360.0,
409            3 => actual_days / 365.0,
410            4 => days_360_between(s, e, true) as f64 / 360.0,
411            _ => unreachable!(),
412        };
413
414        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(
415            sign * frac,
416        )))
417    }
418}
419
420/// Returns the ISO 8601 week number (`1` to `53`) for a date serial.
421///
422/// # Remarks
423/// - Weeks start on Monday and week 1 is the week containing the first Thursday of the year.
424/// - Input serials are truncated to whole dates before evaluation.
425/// - Serials are read using the Excel 1900 date mapping.
426///
427/// # Examples
428/// ```yaml,sandbox
429/// title: "ISO week at year start"
430/// formula: "=ISOWEEKNUM(45292)"
431/// expected: 1
432/// ```
433///
434/// ```yaml,sandbox
435/// title: "ISO week crossing year boundary"
436/// formula: "=ISOWEEKNUM(42370)"
437/// expected: 53
438/// ```
439///
440/// ```yaml,docs
441/// related:
442///   - WEEKNUM
443///   - WEEKDAY
444///   - DATE
445/// faq:
446///   - q: "Can early-January dates return week 52 or 53?"
447///     a: "Yes. ISO week numbering can place dates near New Year in the prior ISO week-year."
448/// ```
449#[derive(Debug)]
450pub struct IsoWeekNumFn;
451
452/// [formualizer-docgen:schema:start]
453/// Name: ISOWEEKNUM
454/// Type: IsoWeekNumFn
455/// Min args: 1
456/// Max args: 1
457/// Variadic: false
458/// Signature: ISOWEEKNUM(arg1: number@scalar)
459/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
460/// Caps: PURE
461/// [formualizer-docgen:schema:end]
462impl Function for IsoWeekNumFn {
463    func_caps!(PURE);
464
465    fn name(&self) -> &'static str {
466        "ISOWEEKNUM"
467    }
468
469    fn min_args(&self) -> usize {
470        1
471    }
472
473    fn arg_schema(&self) -> &'static [ArgSchema] {
474        use std::sync::LazyLock;
475        static ONE: LazyLock<Vec<ArgSchema>> =
476            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
477        &ONE[..]
478    }
479
480    fn eval<'a, 'b, 'c>(
481        &self,
482        args: &'c [ArgumentHandle<'a, 'b>],
483        _ctx: &dyn FunctionContext<'b>,
484    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
485        let d = coerce_to_date(&args[0])?;
486        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
487            d.iso_week().week() as i64,
488        )))
489    }
490}
491
492/// Extracts the calendar year from a date serial.
493///
494/// # Remarks
495/// - Fractional time is ignored; only the integer date portion is used.
496/// - Input serials are interpreted with Excel 1900 date semantics.
497/// - Results are Gregorian calendar years.
498///
499/// # Examples
500/// ```yaml,sandbox
501/// title: "Extract year from date serial"
502/// formula: "=YEAR(44927)"
503/// expected: 2023
504/// ```
505///
506/// ```yaml,sandbox
507/// title: "Extract year from datetime serial"
508/// formula: "=YEAR(45351.75)"
509/// expected: 2024
510/// ```
511///
512/// ```yaml,docs
513/// related:
514///   - MONTH
515///   - DAY
516///   - DATE
517/// faq:
518///   - q: "Does YEAR use the fractional time part of a serial?"
519///     a: "No. YEAR resolves the calendar date and ignores time-of-day fractions."
520/// ```
521#[derive(Debug)]
522pub struct YearFn;
523
524/// [formualizer-docgen:schema:start]
525/// Name: YEAR
526/// Type: YearFn
527/// Min args: 1
528/// Max args: 1
529/// Variadic: false
530/// Signature: YEAR(arg1: number@scalar)
531/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
532/// Caps: PURE
533/// [formualizer-docgen:schema:end]
534impl Function for YearFn {
535    func_caps!(PURE);
536
537    fn name(&self) -> &'static str {
538        "YEAR"
539    }
540
541    fn min_args(&self) -> usize {
542        1
543    }
544
545    fn arg_schema(&self) -> &'static [ArgSchema] {
546        use std::sync::LazyLock;
547        static ONE: LazyLock<Vec<ArgSchema>> =
548            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
549        &ONE[..]
550    }
551
552    fn eval<'a, 'b, 'c>(
553        &self,
554        args: &'c [ArgumentHandle<'a, 'b>],
555        _ctx: &dyn FunctionContext<'b>,
556    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
557        let serial = coerce_to_serial(&args[0])?;
558        let date = serial_to_date(serial)?;
559        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
560            date.year() as i64,
561        )))
562    }
563}
564
565/// Extracts the month number (`1` to `12`) from a date serial.
566///
567/// # Remarks
568/// - Fractional time is ignored; only the date portion contributes.
569/// - Serials are interpreted with Excel 1900 date semantics.
570/// - The result always uses January=`1` through December=`12`.
571///
572/// # Examples
573/// ```yaml,sandbox
574/// title: "Extract month from January date"
575/// formula: "=MONTH(44927)"
576/// expected: 1
577/// ```
578///
579/// ```yaml,sandbox
580/// title: "Extract month from leap-day serial"
581/// formula: "=MONTH(45351)"
582/// expected: 2
583/// ```
584///
585/// ```yaml,docs
586/// related:
587///   - YEAR
588///   - DAY
589///   - EOMONTH
590/// faq:
591///   - q: "Can MONTH return values outside 1 through 12?"
592///     a: "No. MONTH always returns an integer from 1 to 12 after serial-to-date conversion."
593/// ```
594#[derive(Debug)]
595pub struct MonthFn;
596
597/// [formualizer-docgen:schema:start]
598/// Name: MONTH
599/// Type: MonthFn
600/// Min args: 1
601/// Max args: 1
602/// Variadic: false
603/// Signature: MONTH(arg1: number@scalar)
604/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
605/// Caps: PURE
606/// [formualizer-docgen:schema:end]
607impl Function for MonthFn {
608    func_caps!(PURE);
609
610    fn name(&self) -> &'static str {
611        "MONTH"
612    }
613
614    fn min_args(&self) -> usize {
615        1
616    }
617
618    fn arg_schema(&self) -> &'static [ArgSchema] {
619        use std::sync::LazyLock;
620        static ONE: LazyLock<Vec<ArgSchema>> =
621            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
622        &ONE[..]
623    }
624
625    fn eval<'a, 'b, 'c>(
626        &self,
627        args: &'c [ArgumentHandle<'a, 'b>],
628        _ctx: &dyn FunctionContext<'b>,
629    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
630        let serial = coerce_to_serial(&args[0])?;
631        let date = serial_to_date(serial)?;
632        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
633            date.month() as i64,
634        )))
635    }
636}
637
638/// Extracts the day-of-month (`1` to `31`) from a date serial.
639///
640/// # Remarks
641/// - Fractional time is ignored; only the integer serial portion is used.
642/// - Serials are interpreted with Excel 1900 date semantics.
643/// - Output is the day within the month, not day-of-year.
644///
645/// # Examples
646/// ```yaml,sandbox
647/// title: "Extract day from first-of-month"
648/// formula: "=DAY(44927)"
649/// expected: 1
650/// ```
651///
652/// ```yaml,sandbox
653/// title: "Extract day from leap date"
654/// formula: "=DAY(45351)"
655/// expected: 29
656/// ```
657///
658/// ```yaml,docs
659/// related:
660///   - DATE
661///   - MONTH
662///   - EOMONTH
663/// faq:
664///   - q: "Does DAY return day-of-year or day-of-month?"
665///     a: "DAY returns the day number within the month (1-31), not the ordinal day of the year."
666/// ```
667#[derive(Debug)]
668pub struct DayFn;
669
670/// [formualizer-docgen:schema:start]
671/// Name: DAY
672/// Type: DayFn
673/// Min args: 1
674/// Max args: 1
675/// Variadic: false
676/// Signature: DAY(arg1: number@scalar)
677/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
678/// Caps: PURE
679/// [formualizer-docgen:schema:end]
680impl Function for DayFn {
681    func_caps!(PURE);
682
683    fn name(&self) -> &'static str {
684        "DAY"
685    }
686
687    fn min_args(&self) -> usize {
688        1
689    }
690
691    fn arg_schema(&self) -> &'static [ArgSchema] {
692        use std::sync::LazyLock;
693        static ONE: LazyLock<Vec<ArgSchema>> =
694            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
695        &ONE[..]
696    }
697
698    fn eval<'a, 'b, 'c>(
699        &self,
700        args: &'c [ArgumentHandle<'a, 'b>],
701        _ctx: &dyn FunctionContext<'b>,
702    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
703        let serial = coerce_to_serial(&args[0])?;
704        let date = serial_to_date(serial)?;
705        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
706            date.day() as i64,
707        )))
708    }
709}
710
711/// Extracts the hour component (`0` to `23`) from a time or datetime serial.
712///
713/// # Remarks
714/// - For values `>= 1`, only the fractional time part is used.
715/// - For values `< 1`, the value is treated directly as a time fraction.
716/// - Date-system choice does not affect hour extraction because only the fractional part is used.
717///
718/// # Examples
719/// ```yaml,sandbox
720/// title: "Extract hour from noon"
721/// formula: "=HOUR(0.5)"
722/// expected: 12
723/// ```
724///
725/// ```yaml,sandbox
726/// title: "Extract hour from datetime serial"
727/// formula: "=HOUR(45351.75)"
728/// expected: 18
729/// ```
730///
731/// ```yaml,docs
732/// related:
733///   - MINUTE
734///   - SECOND
735///   - TIME
736/// faq:
737///   - q: "Why is HOUR unchanged across 1900 and 1904 date systems?"
738///     a: "HOUR reads only the fractional time component of a serial, so date-system epoch differences do not affect it."
739/// ```
740#[derive(Debug)]
741pub struct HourFn;
742
743/// [formualizer-docgen:schema:start]
744/// Name: HOUR
745/// Type: HourFn
746/// Min args: 1
747/// Max args: 1
748/// Variadic: false
749/// Signature: HOUR(arg1: number@scalar)
750/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
751/// Caps: PURE
752/// [formualizer-docgen:schema:end]
753impl Function for HourFn {
754    func_caps!(PURE);
755
756    fn name(&self) -> &'static str {
757        "HOUR"
758    }
759
760    fn min_args(&self) -> usize {
761        1
762    }
763
764    fn arg_schema(&self) -> &'static [ArgSchema] {
765        use std::sync::LazyLock;
766        static ONE: LazyLock<Vec<ArgSchema>> =
767            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
768        &ONE[..]
769    }
770
771    fn eval<'a, 'b, 'c>(
772        &self,
773        args: &'c [ArgumentHandle<'a, 'b>],
774        _ctx: &dyn FunctionContext<'b>,
775    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
776        let serial = coerce_to_serial(&args[0])?;
777
778        // For time values < 1, we just use the fractional part
779        let time_fraction = if serial < 1.0 { serial } else { serial.fract() };
780
781        // Convert fraction to hours (24 hours = 1.0)
782        let hours = (time_fraction * 24.0) as i64;
783        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(hours)))
784    }
785}
786
787/// Extracts the minute component (`0` to `59`) from a time or datetime serial.
788///
789/// # Remarks
790/// - The integer date portion is ignored for minute extraction.
791/// - Conversion uses Excel 1900 serial interpretation for the date portion when present.
792/// - Time is derived from the fractional serial component.
793///
794/// # Examples
795/// ```yaml,sandbox
796/// title: "Extract minute from 15:30:45"
797/// formula: "=MINUTE(0.6463541667)"
798/// expected: 30
799/// ```
800///
801/// ```yaml,sandbox
802/// title: "Extract minute from exact noon"
803/// formula: "=MINUTE(0.5)"
804/// expected: 0
805/// ```
806///
807/// ```yaml,docs
808/// related:
809///   - HOUR
810///   - SECOND
811///   - TIMEVALUE
812/// faq:
813///   - q: "Does MINUTE round to nearest minute?"
814///     a: "No. MINUTE extracts the minute component from the interpreted time value; it does not independently round to minute precision."
815/// ```
816#[derive(Debug)]
817pub struct MinuteFn;
818
819/// [formualizer-docgen:schema:start]
820/// Name: MINUTE
821/// Type: MinuteFn
822/// Min args: 1
823/// Max args: 1
824/// Variadic: false
825/// Signature: MINUTE(arg1: number@scalar)
826/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
827/// Caps: PURE
828/// [formualizer-docgen:schema:end]
829impl Function for MinuteFn {
830    func_caps!(PURE);
831
832    fn name(&self) -> &'static str {
833        "MINUTE"
834    }
835
836    fn min_args(&self) -> usize {
837        1
838    }
839
840    fn arg_schema(&self) -> &'static [ArgSchema] {
841        use std::sync::LazyLock;
842        static ONE: LazyLock<Vec<ArgSchema>> =
843            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
844        &ONE[..]
845    }
846
847    fn eval<'a, 'b, 'c>(
848        &self,
849        args: &'c [ArgumentHandle<'a, 'b>],
850        _ctx: &dyn FunctionContext<'b>,
851    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
852        let serial = coerce_to_serial(&args[0])?;
853
854        // Extract time component
855        let datetime = serial_to_datetime(serial)?;
856        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
857            datetime.minute() as i64,
858        )))
859    }
860}
861
862/// Extracts the second component (`0` to `59`) from a time or datetime serial.
863///
864/// # Remarks
865/// - The integer date portion is ignored for second extraction.
866/// - Conversion uses Excel 1900 serial interpretation when resolving datetime values.
867/// - Time is computed from the serial fraction and rounded to whole seconds.
868///
869/// # Examples
870/// ```yaml,sandbox
871/// title: "Extract second from 15:30:45"
872/// formula: "=SECOND(0.6463541667)"
873/// expected: 45
874/// ```
875///
876/// ```yaml,sandbox
877/// title: "Extract second from exact noon"
878/// formula: "=SECOND(0.5)"
879/// expected: 0
880/// ```
881///
882/// ```yaml,docs
883/// related:
884///   - HOUR
885///   - MINUTE
886///   - TIMEVALUE
887/// faq:
888///   - q: "Can SECOND return 60 for leap seconds?"
889///     a: "No. SECOND returns values from 0 to 59 based on spreadsheet serial-time interpretation."
890/// ```
891#[derive(Debug)]
892pub struct SecondFn;
893
894/// [formualizer-docgen:schema:start]
895/// Name: SECOND
896/// Type: SecondFn
897/// Min args: 1
898/// Max args: 1
899/// Variadic: false
900/// Signature: SECOND(arg1: number@scalar)
901/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
902/// Caps: PURE
903/// [formualizer-docgen:schema:end]
904impl Function for SecondFn {
905    func_caps!(PURE);
906
907    fn name(&self) -> &'static str {
908        "SECOND"
909    }
910
911    fn min_args(&self) -> usize {
912        1
913    }
914
915    fn arg_schema(&self) -> &'static [ArgSchema] {
916        use std::sync::LazyLock;
917        static ONE: LazyLock<Vec<ArgSchema>> =
918            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar()]);
919        &ONE[..]
920    }
921
922    fn eval<'a, 'b, 'c>(
923        &self,
924        args: &'c [ArgumentHandle<'a, 'b>],
925        _ctx: &dyn FunctionContext<'b>,
926    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
927        let serial = coerce_to_serial(&args[0])?;
928
929        // Extract time component
930        let datetime = serial_to_datetime(serial)?;
931        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
932            datetime.second() as i64,
933        )))
934    }
935}
936
937pub fn register_builtins() {
938    use std::sync::Arc;
939    crate::function_registry::register_function(Arc::new(YearFn));
940    crate::function_registry::register_function(Arc::new(MonthFn));
941    crate::function_registry::register_function(Arc::new(DayFn));
942    crate::function_registry::register_function(Arc::new(HourFn));
943    crate::function_registry::register_function(Arc::new(MinuteFn));
944    crate::function_registry::register_function(Arc::new(SecondFn));
945    crate::function_registry::register_function(Arc::new(DaysFn));
946    crate::function_registry::register_function(Arc::new(Days360Fn));
947    crate::function_registry::register_function(Arc::new(YearFracFn));
948    crate::function_registry::register_function(Arc::new(IsoWeekNumFn));
949}
950
951#[cfg(test)]
952mod tests {
953    use super::*;
954    use crate::test_workbook::TestWorkbook;
955    use formualizer_parse::parser::{ASTNode, ASTNodeType};
956    use std::sync::Arc;
957
958    fn lit(v: LiteralValue) -> ASTNode {
959        ASTNode::new(ASTNodeType::Literal(v), None)
960    }
961
962    #[test]
963    fn test_year_month_day() {
964        let wb = TestWorkbook::new()
965            .with_function(Arc::new(YearFn))
966            .with_function(Arc::new(MonthFn))
967            .with_function(Arc::new(DayFn));
968        let ctx = wb.interpreter();
969
970        // Test with a known date serial number
971        // Serial 44927 = 2023-01-01
972        let serial = lit(LiteralValue::Number(44927.0));
973
974        let year_fn = ctx.context.get_function("", "YEAR").unwrap();
975        let result = year_fn
976            .dispatch(
977                &[ArgumentHandle::new(&serial, &ctx)],
978                &ctx.function_context(None),
979            )
980            .unwrap()
981            .into_literal();
982        assert_eq!(result, LiteralValue::Int(2023));
983
984        let month_fn = ctx.context.get_function("", "MONTH").unwrap();
985        let result = month_fn
986            .dispatch(
987                &[ArgumentHandle::new(&serial, &ctx)],
988                &ctx.function_context(None),
989            )
990            .unwrap()
991            .into_literal();
992        assert_eq!(result, LiteralValue::Int(1));
993
994        let day_fn = ctx.context.get_function("", "DAY").unwrap();
995        let result = day_fn
996            .dispatch(
997                &[ArgumentHandle::new(&serial, &ctx)],
998                &ctx.function_context(None),
999            )
1000            .unwrap()
1001            .into_literal();
1002        assert_eq!(result, LiteralValue::Int(1));
1003    }
1004
1005    #[test]
1006    fn test_hour_minute_second() {
1007        let wb = TestWorkbook::new()
1008            .with_function(Arc::new(HourFn))
1009            .with_function(Arc::new(MinuteFn))
1010            .with_function(Arc::new(SecondFn));
1011        let ctx = wb.interpreter();
1012
1013        // Test with noon (0.5 = 12:00:00)
1014        let serial = lit(LiteralValue::Number(0.5));
1015
1016        let hour_fn = ctx.context.get_function("", "HOUR").unwrap();
1017        let result = hour_fn
1018            .dispatch(
1019                &[ArgumentHandle::new(&serial, &ctx)],
1020                &ctx.function_context(None),
1021            )
1022            .unwrap()
1023            .into_literal();
1024        assert_eq!(result, LiteralValue::Int(12));
1025
1026        let minute_fn = ctx.context.get_function("", "MINUTE").unwrap();
1027        let result = minute_fn
1028            .dispatch(
1029                &[ArgumentHandle::new(&serial, &ctx)],
1030                &ctx.function_context(None),
1031            )
1032            .unwrap()
1033            .into_literal();
1034        assert_eq!(result, LiteralValue::Int(0));
1035
1036        let second_fn = ctx.context.get_function("", "SECOND").unwrap();
1037        let result = second_fn
1038            .dispatch(
1039                &[ArgumentHandle::new(&serial, &ctx)],
1040                &ctx.function_context(None),
1041            )
1042            .unwrap()
1043            .into_literal();
1044        assert_eq!(result, LiteralValue::Int(0));
1045
1046        // Test with 15:30:45 = 15.5/24 + 0.75/24/60 = 0.6463541667
1047        let time_serial = lit(LiteralValue::Number(0.6463541667));
1048
1049        let hour_result = hour_fn
1050            .dispatch(
1051                &[ArgumentHandle::new(&time_serial, &ctx)],
1052                &ctx.function_context(None),
1053            )
1054            .unwrap()
1055            .into_literal();
1056        assert_eq!(hour_result, LiteralValue::Int(15));
1057
1058        let minute_result = minute_fn
1059            .dispatch(
1060                &[ArgumentHandle::new(&time_serial, &ctx)],
1061                &ctx.function_context(None),
1062            )
1063            .unwrap()
1064            .into_literal();
1065        assert_eq!(minute_result, LiteralValue::Int(30));
1066
1067        let second_result = second_fn
1068            .dispatch(
1069                &[ArgumentHandle::new(&time_serial, &ctx)],
1070                &ctx.function_context(None),
1071            )
1072            .unwrap()
1073            .into_literal();
1074        assert_eq!(second_result, LiteralValue::Int(45));
1075    }
1076
1077    #[test]
1078    fn test_year_accepts_date_and_datetime_literals() {
1079        let wb = TestWorkbook::new().with_function(Arc::new(YearFn));
1080        let ctx = wb.interpreter();
1081        let year_fn = ctx.context.get_function("", "YEAR").unwrap();
1082
1083        let date = chrono::NaiveDate::from_ymd_opt(2024, 2, 29).unwrap();
1084        let date_ast = lit(LiteralValue::Date(date));
1085        let from_date = year_fn
1086            .dispatch(
1087                &[ArgumentHandle::new(&date_ast, &ctx)],
1088                &ctx.function_context(None),
1089            )
1090            .unwrap()
1091            .into_literal();
1092        assert_eq!(from_date, LiteralValue::Int(2024));
1093
1094        let dt = date.and_hms_opt(13, 45, 0).unwrap();
1095        let dt_ast = lit(LiteralValue::DateTime(dt));
1096        let from_dt = year_fn
1097            .dispatch(
1098                &[ArgumentHandle::new(&dt_ast, &ctx)],
1099                &ctx.function_context(None),
1100            )
1101            .unwrap()
1102            .into_literal();
1103        assert_eq!(from_dt, LiteralValue::Int(2024));
1104    }
1105
1106    #[test]
1107    fn test_days_and_days360() {
1108        let wb = TestWorkbook::new()
1109            .with_function(Arc::new(DaysFn))
1110            .with_function(Arc::new(Days360Fn));
1111        let ctx = wb.interpreter();
1112
1113        let start = chrono::NaiveDate::from_ymd_opt(2021, 2, 1).unwrap();
1114        let end = chrono::NaiveDate::from_ymd_opt(2021, 3, 15).unwrap();
1115        let start_ast = lit(LiteralValue::Date(start));
1116        let end_ast = lit(LiteralValue::Date(end));
1117
1118        let days_fn = ctx.context.get_function("", "DAYS").unwrap();
1119        let days = days_fn
1120            .dispatch(
1121                &[
1122                    ArgumentHandle::new(&end_ast, &ctx),
1123                    ArgumentHandle::new(&start_ast, &ctx),
1124                ],
1125                &ctx.function_context(None),
1126            )
1127            .unwrap()
1128            .into_literal();
1129        assert_eq!(days, LiteralValue::Number(42.0));
1130
1131        let d360_fn = ctx.context.get_function("", "DAYS360").unwrap();
1132        let s2 = lit(LiteralValue::Date(
1133            chrono::NaiveDate::from_ymd_opt(2011, 1, 31).unwrap(),
1134        ));
1135        let e2 = lit(LiteralValue::Date(
1136            chrono::NaiveDate::from_ymd_opt(2011, 2, 28).unwrap(),
1137        ));
1138        let us = d360_fn
1139            .dispatch(
1140                &[
1141                    ArgumentHandle::new(&s2, &ctx),
1142                    ArgumentHandle::new(&e2, &ctx),
1143                ],
1144                &ctx.function_context(None),
1145            )
1146            .unwrap()
1147            .into_literal();
1148        let eu_flag = lit(LiteralValue::Boolean(true));
1149        let eu = d360_fn
1150            .dispatch(
1151                &[
1152                    ArgumentHandle::new(&s2, &ctx),
1153                    ArgumentHandle::new(&e2, &ctx),
1154                    ArgumentHandle::new(&eu_flag, &ctx),
1155                ],
1156                &ctx.function_context(None),
1157            )
1158            .unwrap()
1159            .into_literal();
1160        assert_eq!(us, LiteralValue::Number(30.0));
1161        assert_eq!(eu, LiteralValue::Number(28.0));
1162    }
1163
1164    #[test]
1165    fn test_yearfrac_and_isoweeknum() {
1166        let wb = TestWorkbook::new()
1167            .with_function(Arc::new(YearFracFn))
1168            .with_function(Arc::new(IsoWeekNumFn));
1169        let ctx = wb.interpreter();
1170
1171        let start = lit(LiteralValue::Date(
1172            chrono::NaiveDate::from_ymd_opt(2021, 1, 1).unwrap(),
1173        ));
1174        let end = lit(LiteralValue::Date(
1175            chrono::NaiveDate::from_ymd_opt(2021, 7, 1).unwrap(),
1176        ));
1177        let basis2 = lit(LiteralValue::Int(2));
1178
1179        let yearfrac_fn = ctx.context.get_function("", "YEARFRAC").unwrap();
1180        let out = yearfrac_fn
1181            .dispatch(
1182                &[
1183                    ArgumentHandle::new(&start, &ctx),
1184                    ArgumentHandle::new(&end, &ctx),
1185                    ArgumentHandle::new(&basis2, &ctx),
1186                ],
1187                &ctx.function_context(None),
1188            )
1189            .unwrap()
1190            .into_literal();
1191
1192        match out {
1193            LiteralValue::Number(v) => assert!((v - (181.0 / 360.0)).abs() < 1e-12),
1194            other => panic!("expected numeric YEARFRAC, got {other:?}"),
1195        }
1196
1197        let iso_fn = ctx.context.get_function("", "ISOWEEKNUM").unwrap();
1198        let d = lit(LiteralValue::Date(
1199            chrono::NaiveDate::from_ymd_opt(2016, 1, 1).unwrap(),
1200        ));
1201        let iso = iso_fn
1202            .dispatch(
1203                &[ArgumentHandle::new(&d, &ctx)],
1204                &ctx.function_context(None),
1205            )
1206            .unwrap()
1207            .into_literal();
1208        assert_eq!(iso, LiteralValue::Int(53));
1209    }
1210}
formualizer_eval/builtins/datetime/date_parts.rs

formualizer_eval/builtins/datetime/
date_parts.rs
