formualizer_eval/builtins/datetime/
date_parts.rs

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

formualizer_eval/builtins/datetime/
date_parts.rs
