1package value23import (4 "strconv"5 "strings"6 "time"78 "src.rest/cat/012/internal/numfmt"9)1011// Now is the clock used by TODAY() and NOW(), and for the year of dates12// typed without one; tests replace it.13var Now = time.Now1415// Typed dates and times become serial day numbers, as in Sheets; see16// numfmt's calendar.1718// ParseDateTime recognizes dates and times typed into a cell the way19// Sheets does in the en-US locale: 9/26/2026, 9/26/26, 9/26 (this year),20// 2026-09-26, Sep 26, 2026, 26 Sep 2026, 14:30, 2:30 PM, 2pm, 25:30 (a21// duration) and a date followed by a time. It returns the serial and the22// format Sheets would apply.23func ParseDateTime(s string) (float64, Format, bool) {24 s = strings.TrimSpace(s)25 if s == "" || !(isDigit(s[0]) || isLetter(s[0])) {26 return 0, Format{}, false27 }28 if t, f, ok := parseTime(s); ok {29 return t, f, true30 }31 // A date, optionally followed by a time after the last space.32 if d, f, ok := parseDate(s); ok {33 return d, f, true34 }35 if i := strings.LastIndexByte(s, ' '); i > 0 {36 datePart, timePart := s[:i], s[i+1:]37 // "2:30 PM" splits the time; move the meridiem back.38 if up := strings.ToUpper(timePart); up == "AM" || up == "PM" {39 if j := strings.LastIndexByte(datePart, ' '); j > 0 {40 datePart, timePart = s[:j], s[j+1:]41 }42 }43 d, _, ok1 := parseDate(datePart)44 t, tf, ok2 := parseTime(timePart)45 if ok1 && ok2 && tf.Kind == FmtTime {46 f := Preset(FmtDateTime)47 if strings.Contains(strings.ToUpper(timePart), "M") {48 f.Pattern = "m/d/yyyy h:mm:ss am/pm"49 }50 return d + t, f, true51 }52 }53 return 0, Format{}, false54}5556// parseTime recognizes H:MM, H:MM:SS(.fff) and H AM/PM forms. Hours of57// 24 or more make a duration.58func parseTime(s string) (float64, Format, bool) {59 clock, meridiem := cutMeridiem(strings.ToUpper(strings.TrimSpace(s)))60 parts := strings.Split(clock, ":")61 if len(parts) > 3 || (len(parts) == 1 && meridiem == "") {62 return 0, Format{}, false63 }64 h, m, sec, ok := clockParts(parts)65 if !ok {66 return 0, Format{}, false67 }68 f := Format{Kind: FmtTime, Pattern: "h:mm:ss"}69 switch {70 case meridiem != "":71 if h < 1 || h > 12 {72 return 0, Format{}, false73 }74 h %= 1275 if meridiem == "P" {76 h += 1277 }78 f = Preset(FmtTime)79 case h >= 24:80 f = Preset(FmtDuration)81 }82 return numfmt.TimeSerial(h, m, sec), f, true83}8485// cutMeridiem cuts a trailing AM or PM off an upper-case time, returning86// the rest and "A", "P" or "".87func cutMeridiem(up string) (string, string) {88 for _, m := range [...]string{"AM", "PM"} {89 if rest, ok := strings.CutSuffix(up, m); ok {90 return strings.TrimSpace(rest), m[:1]91 }92 }93 return up, ""94}9596// clockParts reads the parts of H:MM:SS: hours of any number of digits,97// then minutes of two digits and seconds of two, which may have decimals.98func clockParts(parts []string) (h, m int, sec float64, ok bool) {99 for i, p := range parts {100 if p == "" || !isDigit(p[0]) {101 return 0, 0, 0, false102 }103 whole := strings.Trim(p, "0123456789") == ""104 switch i {105 case 0:106 h, _ = strconv.Atoi(p)107 ok = whole108 case 1:109 m, _ = strconv.Atoi(p)110 ok = whole && len(p) == 2 && m < 60111 default:112 v, err := strconv.ParseFloat(p, 64)113 sec = v114 ok = err == nil && v < 60 && !strings.ContainsAny(p, "eE+-")115 }116 if !ok {117 return 0, 0, 0, false118 }119 }120 return h, m, sec, true121}122123// parseDate recognizes the date forms listed at ParseDateTime.124func parseDate(s string) (float64, Format, bool) {125 s = strings.TrimSpace(s)126 switch {127 case strings.Count(s, "/") >= 1 && strings.Trim(s, "0123456789/") == "":128 p := strings.Split(s, "/")129 if len(p[0]) == 4 && len(p) == 3 { // 2026/09/26130 return ymd(p[0], p[1], p[2], Format{Kind: FmtDate, Pattern: "yyyy/mm/dd"})131 }132 switch len(p) {133 case 2:134 return ymd(strconv.Itoa(Now().Year()), p[0], p[1], Preset(FmtDate))135 case 3:136 return ymd(p[2], p[0], p[1], Preset(FmtDate))137 }138 case strings.Count(s, "-") == 2 && strings.Trim(s, "0123456789-") == "":139 p := strings.Split(s, "-")140 if len(p[0]) == 4 { // ISO 2026-09-26141 return ymd(p[0], p[1], p[2], Format{Kind: FmtDate, Pattern: "yyyy-mm-dd"})142 }143 return ymd(p[2], p[0], p[1], Preset(FmtDate))144 }145 // Month names: "Sep 26, 2026", "September 26 2026", "26 Sep 2026", "Sep 26".146 words := strings.Fields(strings.ReplaceAll(s, ",", " "))147 if len(words) < 2 || len(words) > 3 {148 return 0, Format{}, false149 }150 year := strconv.Itoa(Now().Year())151 if len(words) == 3 {152 year = words[2]153 }154 if m, long, ok := monthName(words[0]); ok {155 f := Format{Kind: FmtDate, Pattern: "mmm d, yyyy"}156 if long {157 f.Pattern = "mmmm d, yyyy"158 }159 return ymd(year, strconv.Itoa(m), words[1], f)160 }161 if m, long, ok := monthName(words[1]); ok {162 f := Format{Kind: FmtDate, Pattern: "d mmm yyyy"}163 if long {164 f.Pattern = "d mmmm yyyy"165 }166 return ymd(year, strconv.Itoa(m), words[0], f)167 }168 return 0, Format{}, false169}170171// monthName matches a month's full name or three-letter abbreviation.172func monthName(w string) (month int, long, ok bool) {173 w = strings.ToLower(strings.TrimSuffix(w, "."))174 for i, n := range numfmt.MonthNames {175 n = strings.ToLower(n)176 if w == n {177 return i + 1, len(w) > 3, true178 }179 if w == n[:3] {180 return i + 1, false, true181 }182 }183 return 0, false, false184}185186// ymd validates and converts date parts. Two-digit years 00-29 are187// 2000-2029 and 30-99 are 1930-1999, as in Sheets.188func ymd(ys, ms, ds string, f Format) (float64, Format, bool) {189 y, err1 := strconv.Atoi(ys)190 m, err2 := strconv.Atoi(ms)191 d, err3 := strconv.Atoi(ds)192 if err1 != nil || err2 != nil || err3 != nil || len(ys) == 3 || len(ys) > 4 || len(ms) > 2 || len(ds) > 2 {193 return 0, Format{}, false194 }195 if len(ys) <= 2 {196 if y < 30 {197 y += 2000198 } else {199 y += 1900200 }201 }202 if m < 1 || m > 12 || d < 1 || d > numfmt.DaysIn(y, m) {203 return 0, Format{}, false204 }205 return numfmt.DateSerial(y, m, d), f, true206}