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, Timelike};
6
7use crate::cell::{
8    date_to_serial, datetime_to_serial, serial_to_date, serial_to_datetime, CellValue,
9};
10use crate::error::Result;
11use crate::formula::ast::Expr;
12use crate::formula::eval::{coerce_to_number, coerce_to_string, Evaluator};
13use crate::formula::functions::check_arg_count;
14
15/// Convert a CellValue to an Excel serial number.
16fn to_serial(v: &CellValue) -> std::result::Result<f64, CellValue> {
17    match v {
18        CellValue::Number(n) => Ok(*n),
19        CellValue::Date(n) => Ok(*n),
20        CellValue::String(s) => {
21            if let Ok(n) = s.parse::<f64>() {
22                Ok(n)
23            } else if let Some(d) = parse_date_string(s) {
24                Ok(date_to_serial(d))
25            } else {
26                Err(CellValue::Error("#VALUE!".to_string()))
27            }
28        }
29        CellValue::Bool(b) => Ok(if *b { 1.0 } else { 0.0 }),
30        CellValue::Empty => Ok(0.0),
31        _ => Err(CellValue::Error("#VALUE!".to_string())),
32    }
33}
34
35/// Parse common date string formats: YYYY-MM-DD and MM/DD/YYYY.
36fn parse_date_string(s: &str) -> Option<NaiveDate> {
37    if let Ok(d) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
38        return Some(d);
39    }
40    if let Ok(d) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
41        return Some(d);
42    }
43    None
44}
45
46/// Add months to a date, clamping to end of month if needed.
47fn add_months_to_date(date: NaiveDate, months: i32) -> Option<NaiveDate> {
48    let total_months = date.year() * 12 + date.month() as i32 - 1 + months;
49    let new_year = total_months.div_euclid(12);
50    let new_month = (total_months.rem_euclid(12) + 1) as u32;
51    let max_day = last_day_of_month(new_year, new_month);
52    let new_day = date.day().min(max_day);
53    NaiveDate::from_ymd_opt(new_year, new_month, new_day)
54}
55
56/// Return the last day of the given month.
57fn last_day_of_month(year: i32, month: u32) -> u32 {
58    match month {
59        1 | 3 | 5 | 7 | 8 | 10 | 12 => 31,
60        4 | 6 | 9 | 11 => 30,
61        2 => {
62            if (year % 4 == 0 && year % 100 != 0) || year % 400 == 0 {
63                29
64            } else {
65                28
66            }
67        }
68        _ => 30,
69    }
70}
71
72/// DATE(year, month, day) - constructs a date serial number.
73pub fn fn_date(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
74    check_arg_count("DATE", args, 3, 3)?;
75    let year = coerce_to_number(&ctx.eval_expr(&args[0])?)? as i32;
76    let month = coerce_to_number(&ctx.eval_expr(&args[1])?)? as i32;
77    let day = coerce_to_number(&ctx.eval_expr(&args[2])?)? as i32;
78
79    let adj_year = if year < 1900 { year + 1900 } else { year };
80
81    // Handle month overflow/underflow
82    let base = NaiveDate::from_ymd_opt(adj_year, 1, 1);
83    let base = match base {
84        Some(d) => d,
85        None => return Ok(CellValue::Error("#VALUE!".to_string())),
86    };
87    let with_months = add_months_to_date(base, month - 1);
88    let with_months = match with_months {
89        Some(d) => d,
90        None => return Ok(CellValue::Error("#VALUE!".to_string())),
91    };
92    let result = with_months + Duration::days(i64::from(day) - 1);
93    Ok(CellValue::Date(date_to_serial(result)))
94}
95
96/// TODAY() - returns today's date serial number.
97pub fn fn_today(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
98    check_arg_count("TODAY", args, 0, 0)?;
99    let today = Local::now().date_naive();
100    Ok(CellValue::Date(date_to_serial(today)))
101}
102
103/// NOW() - returns current date and time serial number.
104pub fn fn_now(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
105    check_arg_count("NOW", args, 0, 0)?;
106    let now = Local::now().naive_local();
107    Ok(CellValue::Date(datetime_to_serial(now)))
108}
109
110/// YEAR(serial_number) - extracts the year from a date serial.
111pub fn fn_year(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
112    check_arg_count("YEAR", args, 1, 1)?;
113    let v = ctx.eval_expr(&args[0])?;
114    match to_serial(&v) {
115        Ok(serial) => match serial_to_date(serial) {
116            Some(d) => Ok(CellValue::Number(d.year() as f64)),
117            None => Ok(CellValue::Error("#VALUE!".to_string())),
118        },
119        Err(e) => Ok(e),
120    }
121}
122
123/// MONTH(serial_number) - extracts the month from a date serial.
124pub fn fn_month(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
125    check_arg_count("MONTH", args, 1, 1)?;
126    let v = ctx.eval_expr(&args[0])?;
127    match to_serial(&v) {
128        Ok(serial) => match serial_to_date(serial) {
129            Some(d) => Ok(CellValue::Number(d.month() as f64)),
130            None => Ok(CellValue::Error("#VALUE!".to_string())),
131        },
132        Err(e) => Ok(e),
133    }
134}
135
136/// DAY(serial_number) - extracts the day from a date serial.
137pub fn fn_day(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
138    check_arg_count("DAY", args, 1, 1)?;
139    let v = ctx.eval_expr(&args[0])?;
140    match to_serial(&v) {
141        Ok(serial) => match serial_to_date(serial) {
142            Some(d) => Ok(CellValue::Number(d.day() as f64)),
143            None => Ok(CellValue::Error("#VALUE!".to_string())),
144        },
145        Err(e) => Ok(e),
146    }
147}
148
149/// HOUR(serial_number) - extracts the hour from a datetime serial.
150pub fn fn_hour(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
151    check_arg_count("HOUR", args, 1, 1)?;
152    let v = ctx.eval_expr(&args[0])?;
153    match to_serial(&v) {
154        Ok(serial) => match serial_to_datetime(serial) {
155            Some(dt) => Ok(CellValue::Number(dt.hour() as f64)),
156            None => Ok(CellValue::Error("#VALUE!".to_string())),
157        },
158        Err(e) => Ok(e),
159    }
160}
161
162/// MINUTE(serial_number) - extracts the minute from a datetime serial.
163pub fn fn_minute(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
164    check_arg_count("MINUTE", args, 1, 1)?;
165    let v = ctx.eval_expr(&args[0])?;
166    match to_serial(&v) {
167        Ok(serial) => match serial_to_datetime(serial) {
168            Some(dt) => Ok(CellValue::Number(dt.minute() as f64)),
169            None => Ok(CellValue::Error("#VALUE!".to_string())),
170        },
171        Err(e) => Ok(e),
172    }
173}
174
175/// SECOND(serial_number) - extracts the second from a datetime serial.
176pub fn fn_second(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
177    check_arg_count("SECOND", args, 1, 1)?;
178    let v = ctx.eval_expr(&args[0])?;
179    match to_serial(&v) {
180        Ok(serial) => match serial_to_datetime(serial) {
181            Some(dt) => Ok(CellValue::Number(dt.second() as f64)),
182            None => Ok(CellValue::Error("#VALUE!".to_string())),
183        },
184        Err(e) => Ok(e),
185    }
186}
187
188/// DATEDIF(start_date, end_date, unit) - difference between two dates.
189pub fn fn_datedif(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
190    check_arg_count("DATEDIF", args, 3, 3)?;
191    let v1 = ctx.eval_expr(&args[0])?;
192    let v2 = ctx.eval_expr(&args[1])?;
193    let unit = coerce_to_string(&ctx.eval_expr(&args[2])?).to_ascii_uppercase();
194
195    let s1 = match to_serial(&v1) {
196        Ok(n) => n,
197        Err(e) => return Ok(e),
198    };
199    let s2 = match to_serial(&v2) {
200        Ok(n) => n,
201        Err(e) => return Ok(e),
202    };
203    if s1 > s2 {
204        return Ok(CellValue::Error("#NUM!".to_string()));
205    }
206
207    let d1 = match serial_to_date(s1) {
208        Some(d) => d,
209        None => return Ok(CellValue::Error("#VALUE!".to_string())),
210    };
211    let d2 = match serial_to_date(s2) {
212        Some(d) => d,
213        None => return Ok(CellValue::Error("#VALUE!".to_string())),
214    };
215
216    let result = match unit.as_str() {
217        "Y" => {
218            let mut years = d2.year() - d1.year();
219            if (d2.month(), d2.day()) < (d1.month(), d1.day()) {
220                years -= 1;
221            }
222            years as f64
223        }
224        "M" => {
225            let mut months = (d2.year() - d1.year()) * 12 + d2.month() as i32 - d1.month() as i32;
226            if d2.day() < d1.day() {
227                months -= 1;
228            }
229            months as f64
230        }
231        "D" => s2.floor() - s1.floor(),
232        "YM" => {
233            let mut months = d2.month() as i32 - d1.month() as i32;
234            if d2.day() < d1.day() {
235                months -= 1;
236            }
237            if months < 0 {
238                months += 12;
239            }
240            months as f64
241        }
242        "YD" => {
243            let mut d1_this_year = NaiveDate::from_ymd_opt(
244                d2.year(),
245                d1.month(),
246                d1.day().min(last_day_of_month(d2.year(), d1.month())),
247            );
248            let d1_this_year = match d1_this_year.take() {
249                Some(d) => d,
250                None => return Ok(CellValue::Error("#VALUE!".to_string())),
251            };
252            let days = if d2 >= d1_this_year {
253                (d2 - d1_this_year).num_days()
254            } else {
255                let d1_last_year = NaiveDate::from_ymd_opt(
256                    d2.year() - 1,
257                    d1.month(),
258                    d1.day().min(last_day_of_month(d2.year() - 1, d1.month())),
259                )
260                .unwrap();
261                (d2 - d1_last_year).num_days()
262            };
263            days as f64
264        }
265        "MD" => {
266            let mut days = d2.day() as i32 - d1.day() as i32;
267            if days < 0 {
268                let prev_month_end = if d2.month() == 1 {
269                    last_day_of_month(d2.year() - 1, 12)
270                } else {
271                    last_day_of_month(d2.year(), d2.month() - 1)
272                };
273                days += prev_month_end as i32;
274            }
275            days as f64
276        }
277        _ => return Ok(CellValue::Error("#NUM!".to_string())),
278    };
279    Ok(CellValue::Number(result))
280}
281
282/// EDATE(start_date, months) - date that is N months before/after start_date.
283pub fn fn_edate(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
284    check_arg_count("EDATE", args, 2, 2)?;
285    let v = ctx.eval_expr(&args[0])?;
286    let months = coerce_to_number(&ctx.eval_expr(&args[1])?)? as i32;
287    let serial = match to_serial(&v) {
288        Ok(n) => n,
289        Err(e) => return Ok(e),
290    };
291    let date = match serial_to_date(serial) {
292        Some(d) => d,
293        None => return Ok(CellValue::Error("#VALUE!".to_string())),
294    };
295    match add_months_to_date(date, months) {
296        Some(d) => Ok(CellValue::Date(date_to_serial(d))),
297        None => Ok(CellValue::Error("#VALUE!".to_string())),
298    }
299}
300
301/// EOMONTH(start_date, months) - last day of the month N months away.
302pub fn fn_eomonth(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
303    check_arg_count("EOMONTH", args, 2, 2)?;
304    let v = ctx.eval_expr(&args[0])?;
305    let months = coerce_to_number(&ctx.eval_expr(&args[1])?)? as i32;
306    let serial = match to_serial(&v) {
307        Ok(n) => n,
308        Err(e) => return Ok(e),
309    };
310    let date = match serial_to_date(serial) {
311        Some(d) => d,
312        None => return Ok(CellValue::Error("#VALUE!".to_string())),
313    };
314    match add_months_to_date(date, months) {
315        Some(d) => {
316            let eom = last_day_of_month(d.year(), d.month());
317            let result = NaiveDate::from_ymd_opt(d.year(), d.month(), eom).unwrap();
318            Ok(CellValue::Date(date_to_serial(result)))
319        }
320        None => Ok(CellValue::Error("#VALUE!".to_string())),
321    }
322}
323
324/// DATEVALUE(date_text) - converts a date string to a serial number.
325pub fn fn_datevalue(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
326    check_arg_count("DATEVALUE", args, 1, 1)?;
327    let text = coerce_to_string(&ctx.eval_expr(&args[0])?);
328    match parse_date_string(&text) {
329        Some(d) => Ok(CellValue::Date(date_to_serial(d))),
330        None => Ok(CellValue::Error("#VALUE!".to_string())),
331    }
332}
333
334/// WEEKDAY(serial_number, [return_type]) - day of the week.
335pub fn fn_weekday(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
336    check_arg_count("WEEKDAY", args, 1, 2)?;
337    let v = ctx.eval_expr(&args[0])?;
338    let return_type = if args.len() > 1 {
339        coerce_to_number(&ctx.eval_expr(&args[1])?)? as i32
340    } else {
341        1
342    };
343    let serial = match to_serial(&v) {
344        Ok(n) => n,
345        Err(e) => return Ok(e),
346    };
347    let date = match serial_to_date(serial) {
348        Some(d) => d,
349        None => return Ok(CellValue::Error("#VALUE!".to_string())),
350    };
351    // chrono: Mon=1 .. Sun=7
352    let weekday_num = date.weekday().num_days_from_monday(); // Mon=0 .. Sun=6
353    let result = match return_type {
354        1 => ((weekday_num + 1) % 7) + 1, // Sun=1, Mon=2, ..., Sat=7
355        2 => weekday_num + 1,             // Mon=1, ..., Sun=7
356        3 => weekday_num,                 // Mon=0, ..., Sun=6
357        _ => return Ok(CellValue::Error("#NUM!".to_string())),
358    };
359    Ok(CellValue::Number(result as f64))
360}
361
362/// WEEKNUM(serial_number, [return_type]) - week number of the year.
363pub fn fn_weeknum(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
364    check_arg_count("WEEKNUM", args, 1, 2)?;
365    let v = ctx.eval_expr(&args[0])?;
366    let return_type = if args.len() > 1 {
367        coerce_to_number(&ctx.eval_expr(&args[1])?)? as i32
368    } else {
369        1
370    };
371    let serial = match to_serial(&v) {
372        Ok(n) => n,
373        Err(e) => return Ok(e),
374    };
375    let date = match serial_to_date(serial) {
376        Some(d) => d,
377        None => return Ok(CellValue::Error("#VALUE!".to_string())),
378    };
379    let jan1 = NaiveDate::from_ymd_opt(date.year(), 1, 1).unwrap();
380    let jan1_weekday = jan1.weekday().num_days_from_monday(); // Mon=0..Sun=6
381    let day_of_year = date.ordinal() as i32;
382
383    let week_start_offset = match return_type {
384        1 => (jan1_weekday + 1) % 7, // Sunday-based
385        2 => jan1_weekday,           // Monday-based
386        _ => return Ok(CellValue::Error("#NUM!".to_string())),
387    };
388    let week = (day_of_year - 1 + week_start_offset as i32) / 7 + 1;
389    Ok(CellValue::Number(week as f64))
390}
391
392/// NETWORKDAYS(start_date, end_date, [holidays]) - working days between two dates.
393pub fn fn_networkdays(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
394    check_arg_count("NETWORKDAYS", args, 2, 3)?;
395    let v1 = ctx.eval_expr(&args[0])?;
396    let v2 = ctx.eval_expr(&args[1])?;
397    let s1 = match to_serial(&v1) {
398        Ok(n) => n,
399        Err(e) => return Ok(e),
400    };
401    let s2 = match to_serial(&v2) {
402        Ok(n) => n,
403        Err(e) => return Ok(e),
404    };
405    let d1 = match serial_to_date(s1) {
406        Some(d) => d,
407        None => return Ok(CellValue::Error("#VALUE!".to_string())),
408    };
409    let d2 = match serial_to_date(s2) {
410        Some(d) => d,
411        None => return Ok(CellValue::Error("#VALUE!".to_string())),
412    };
413
414    let (start, end, sign) = if d1 <= d2 {
415        (d1, d2, 1i32)
416    } else {
417        (d2, d1, -1i32)
418    };
419
420    let mut count = 0i32;
421    let mut current = start;
422    while current <= end {
423        let wd = current.weekday().num_days_from_monday();
424        if wd < 5 {
425            count += 1;
426        }
427        current += Duration::days(1);
428    }
429    Ok(CellValue::Number((count * sign) as f64))
430}
431
432/// WORKDAY(start_date, days, [holidays]) - date after N working days.
433pub fn fn_workday(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
434    check_arg_count("WORKDAY", args, 2, 3)?;
435    let v = ctx.eval_expr(&args[0])?;
436    let days = coerce_to_number(&ctx.eval_expr(&args[1])?)? as i32;
437    let serial = match to_serial(&v) {
438        Ok(n) => n,
439        Err(e) => return Ok(e),
440    };
441    let start = match serial_to_date(serial) {
442        Some(d) => d,
443        None => return Ok(CellValue::Error("#VALUE!".to_string())),
444    };
445
446    let step = if days >= 0 { 1i64 } else { -1i64 };
447    let mut remaining = days.unsigned_abs() as i32;
448    let mut current = start;
449    while remaining > 0 {
450        current += Duration::days(step);
451        let wd = current.weekday().num_days_from_monday();
452        if wd < 5 {
453            remaining -= 1;
454        }
455    }
456    Ok(CellValue::Date(date_to_serial(current)))
457}
458
459#[cfg(test)]
460mod tests {
461    use super::*;
462    use crate::cell::date_to_serial;
463    use crate::formula::eval::{evaluate, CellSnapshot};
464    use crate::formula::parser::parse_formula;
465
466    fn eval(formula: &str) -> CellValue {
467        let snap = CellSnapshot::new("Sheet1".to_string());
468        let expr = parse_formula(formula).unwrap();
469        evaluate(&expr, &snap).unwrap()
470    }
471
472    #[test]
473    fn test_date_basic() {
474        let result = eval("DATE(2024,1,15)");
475        let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 15).unwrap());
476        assert_eq!(result, CellValue::Date(expected));
477    }
478
479    #[test]
480    fn test_date_month_overflow() {
481        let result = eval("DATE(2024,13,1)");
482        let expected = date_to_serial(NaiveDate::from_ymd_opt(2025, 1, 1).unwrap());
483        assert_eq!(result, CellValue::Date(expected));
484    }
485
486    #[test]
487    fn test_year() {
488        assert_eq!(eval("YEAR(DATE(2024,6,15))"), CellValue::Number(2024.0));
489    }
490
491    #[test]
492    fn test_month() {
493        assert_eq!(eval("MONTH(DATE(2024,6,15))"), CellValue::Number(6.0));
494    }
495
496    #[test]
497    fn test_day() {
498        assert_eq!(eval("DAY(DATE(2024,6,15))"), CellValue::Number(15.0));
499    }
500
501    #[test]
502    fn test_hour() {
503        // Serial 36526.5 = 2000-01-01 12:00:00
504        assert_eq!(eval("HOUR(36526.5)"), CellValue::Number(12.0));
505    }
506
507    #[test]
508    fn test_minute() {
509        // 36526 + 14*3600 + 30*60 = time fraction
510        // 14:30 = (14*3600 + 30*60) / 86400 = 52200/86400 = 0.604166...
511        let serial = 36526.0 + 52200.0 / 86400.0;
512        let formula = format!("MINUTE({serial})");
513        assert_eq!(eval(&formula), CellValue::Number(30.0));
514    }
515
516    #[test]
517    fn test_second() {
518        // 36526 + 14*3600 + 30*60 + 45 = time fraction
519        let serial = 36526.0 + (14.0 * 3600.0 + 30.0 * 60.0 + 45.0) / 86400.0;
520        let formula = format!("SECOND({serial})");
521        assert_eq!(eval(&formula), CellValue::Number(45.0));
522    }
523
524    #[test]
525    fn test_datedif_years() {
526        assert_eq!(
527            eval("DATEDIF(DATE(2020,1,1),DATE(2024,6,15),\"Y\")"),
528            CellValue::Number(4.0)
529        );
530    }
531
532    #[test]
533    fn test_datedif_months() {
534        assert_eq!(
535            eval("DATEDIF(DATE(2024,1,1),DATE(2024,6,15),\"M\")"),
536            CellValue::Number(5.0)
537        );
538    }
539
540    #[test]
541    fn test_datedif_days() {
542        assert_eq!(
543            eval("DATEDIF(DATE(2024,1,1),DATE(2024,1,31),\"D\")"),
544            CellValue::Number(30.0)
545        );
546    }
547
548    #[test]
549    fn test_edate() {
550        let result = eval("EDATE(DATE(2024,1,31),1)");
551        let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 2, 29).unwrap());
552        assert_eq!(result, CellValue::Date(expected));
553    }
554
555    #[test]
556    fn test_eomonth() {
557        let result = eval("EOMONTH(DATE(2024,1,15),1)");
558        let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 2, 29).unwrap());
559        assert_eq!(result, CellValue::Date(expected));
560    }
561
562    #[test]
563    fn test_datevalue() {
564        let result = eval(r#"DATEVALUE("2024-06-15")"#);
565        let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
566        assert_eq!(result, CellValue::Date(expected));
567    }
568
569    #[test]
570    fn test_weekday_type1() {
571        // 2024-01-15 is a Monday
572        assert_eq!(eval("WEEKDAY(DATE(2024,1,15),1)"), CellValue::Number(2.0));
573    }
574
575    #[test]
576    fn test_weekday_type2() {
577        // Monday = 1 in type 2
578        assert_eq!(eval("WEEKDAY(DATE(2024,1,15),2)"), CellValue::Number(1.0));
579    }
580
581    #[test]
582    fn test_weeknum() {
583        // 2024-01-15 is in week 3 (Sunday-based)
584        assert_eq!(eval("WEEKNUM(DATE(2024,1,15),1)"), CellValue::Number(3.0));
585    }
586
587    #[test]
588    fn test_networkdays() {
589        // Jan 1 2024 (Mon) to Jan 5 2024 (Fri) = 5 working days
590        assert_eq!(
591            eval("NETWORKDAYS(DATE(2024,1,1),DATE(2024,1,5))"),
592            CellValue::Number(5.0)
593        );
594    }
595
596    #[test]
597    fn test_workday() {
598        // Start on Fri Jan 5 2024, add 1 workday = Mon Jan 8 2024
599        let result = eval("WORKDAY(DATE(2024,1,5),1)");
600        let expected = date_to_serial(NaiveDate::from_ymd_opt(2024, 1, 8).unwrap());
601        assert_eq!(result, CellValue::Date(expected));
602    }
603}
sheetkit_core/formula/functions/date_time.rs

sheetkit_core/formula/functions/
date_time.rs
