ironcalc_base/formatter/
dates.rs

1use chrono::Datelike;
2use chrono::Days;
3use chrono::Duration;
4use chrono::Months;
5use chrono::NaiveDate;
6
7use crate::constants::EXCEL_DATE_BASE;
8use crate::constants::MAXIMUM_DATE_SERIAL_NUMBER;
9use crate::constants::MINIMUM_DATE_SERIAL_NUMBER;
10
11pub const DATE_OUT_OF_RANGE_MESSAGE: &str = "Out of range parameters for date";
12
13#[inline]
14fn convert_to_serial_number(date: NaiveDate) -> i32 {
15    date.num_days_from_ce() - EXCEL_DATE_BASE
16}
17
18fn is_date_within_range(date: NaiveDate) -> bool {
19    convert_to_serial_number(date) >= MINIMUM_DATE_SERIAL_NUMBER
20        && convert_to_serial_number(date) <= MAXIMUM_DATE_SERIAL_NUMBER
21}
22
23pub fn from_excel_date(days: i64) -> Result<NaiveDate, String> {
24    if days < MINIMUM_DATE_SERIAL_NUMBER as i64 {
25        return Err(format!(
26            "Excel date must be greater than {MINIMUM_DATE_SERIAL_NUMBER}"
27        ));
28    };
29    if days > MAXIMUM_DATE_SERIAL_NUMBER as i64 {
30        return Err(format!(
31            "Excel date must be less than {MAXIMUM_DATE_SERIAL_NUMBER}"
32        ));
33    };
34    #[allow(clippy::expect_used)]
35    let dt = NaiveDate::from_ymd_opt(1900, 1, 1).expect("problem with chrono::NaiveDate");
36    Ok(dt + Duration::days(days - 2))
37}
38
39pub fn date_to_serial_number(day: u32, month: u32, year: i32) -> Result<i32, String> {
40    match NaiveDate::from_ymd_opt(year, month, day) {
41        Some(native_date) => Ok(convert_to_serial_number(native_date)),
42        None => Err(DATE_OUT_OF_RANGE_MESSAGE.to_string()),
43    }
44}
45
46pub fn permissive_date_to_serial_number(day: i32, month: i32, year: i32) -> Result<i32, String> {
47    // Excel parses `DATE` very permissively. It allows not just for valid date values, but it
48    // allows for invalid dates as well. If you for example enter `DATE(1900, 1, 32)` it will
49    // return the date `1900-02-01`. Despite giving a day that is out of range it will just
50    // wrap the month and year around.
51    //
52    // This function applies that same logic to dates. And does it in the most compatible way as
53    // possible.
54
55    // Special case for the minimum date
56    if year == 1899 && month == 12 && day == 31 {
57        return Ok(MINIMUM_DATE_SERIAL_NUMBER);
58    }
59    let Some(mut date) = NaiveDate::from_ymd_opt(year, 1, 1) else {
60        return Err(DATE_OUT_OF_RANGE_MESSAGE.to_string());
61    };
62
63    // One thing to note for example is that even if you started with a year out of range
64    // but tried to increment the months so that it wraps around into within range, excel
65    // would still return an error.
66    //
67    // I.E. DATE(0,13,-1) will return an error, despite it being equivalent to DATE(1,1,0) which
68    // is within range.
69    //
70    // As a result, we have to run range checks as we parse the date from the biggest unit to the
71    // smallest unit.
72    if !is_date_within_range(date) {
73        return Err(DATE_OUT_OF_RANGE_MESSAGE.to_string());
74    }
75
76    date = {
77        let month_diff = month - 1;
78        let abs_month = month_diff.unsigned_abs();
79        if month_diff <= 0 {
80            date = date - Months::new(abs_month);
81        } else {
82            date = date + Months::new(abs_month);
83        }
84        if !is_date_within_range(date) {
85            return Err(DATE_OUT_OF_RANGE_MESSAGE.to_string());
86        }
87        date
88    };
89
90    date = {
91        let day_diff = day - 1;
92        let abs_day = day_diff.unsigned_abs() as u64;
93        if day_diff <= 0 {
94            date = date - Days::new(abs_day);
95        } else {
96            date = date + Days::new(abs_day);
97        }
98        if !is_date_within_range(date) {
99            return Err(DATE_OUT_OF_RANGE_MESSAGE.to_string());
100        }
101        date
102    };
103
104    Ok(convert_to_serial_number(date))
105}
106
107#[cfg(test)]
108mod tests {
109    use super::*;
110
111    #[test]
112    fn test_permissive_date_to_serial_number() {
113        assert_eq!(
114            permissive_date_to_serial_number(42, 42, 2002),
115            date_to_serial_number(12, 7, 2005)
116        );
117        assert_eq!(
118            permissive_date_to_serial_number(1, 42, 2002),
119            date_to_serial_number(1, 6, 2005)
120        );
121        assert_eq!(
122            permissive_date_to_serial_number(1, 15, 2000),
123            date_to_serial_number(1, 3, 2001)
124        );
125        assert_eq!(
126            permissive_date_to_serial_number(1, 49, 2000),
127            date_to_serial_number(1, 1, 2004)
128        );
129        assert_eq!(
130            permissive_date_to_serial_number(1, 49, 2000),
131            date_to_serial_number(1, 1, 2004)
132        );
133        assert_eq!(
134            permissive_date_to_serial_number(31, 49, 2000),
135            date_to_serial_number(31, 1, 2004)
136        );
137        assert_eq!(
138            permissive_date_to_serial_number(256, 49, 2000),
139            date_to_serial_number(12, 9, 2004)
140        );
141        assert_eq!(
142            permissive_date_to_serial_number(256, 1, 2004),
143            date_to_serial_number(12, 9, 2004)
144        );
145    }
146
147    #[test]
148    fn test_max_and_min_dates() {
149        assert_eq!(
150            permissive_date_to_serial_number(31, 12, 9999),
151            Ok(MAXIMUM_DATE_SERIAL_NUMBER),
152        );
153        assert_eq!(
154            permissive_date_to_serial_number(31, 12, 1899),
155            Ok(MINIMUM_DATE_SERIAL_NUMBER),
156        );
157    }
158}
ironcalc_base/formatter/dates.rs

ironcalc_base/formatter/
dates.rs
