01 February 2021

Date and time is stored in excel as integer of float64 value. When you read them in clojure, you need to convert it to date/date-time object.

For example, "2020-11-17T08:09:10.000" is stored as 44152.33969907 in Excel low level.

You can use cljc.java-time 1 to handle date and date time values.

Firstly, add the deps.

clj deps.edn:

 {cljc.java-time/cljc.java-time               {:mvn/version "0.1.15"}}}

or leiningen project.clj:

(defproject excel-test "0.0.1"
  :dependencies [[cljc.java-time "0.1.15"]])

You can use the latest version:

Then let's calculate the 44152 or 44152.33969907 in local date or local date time.

There are a couple of additional caveats to be aware of before we try to convert the dates:

  • Excel counts 1900-01-01 as day 1 in it’s serial calculation, so you need to ensure you are counting this extra day in any date difference calculations.
  • The Excel date serial is off by 1, as it behaves as if 1900-02-29 existed. This is due to a compatibility issue with Lotus 123 back when Excel was released and is a known issue2.

This means that the Excel date serial for a given day is off by 2 compared to the actual amount of days that have elapsed since 1900-01-01.3 Thus, you can calculate the date base on 1899-12-30.

(require '[cljc.java-time.local-date :as ld]
         '[cljc.java-time.local-date-time :as ldt]
         '[cljc.java-time.temporal.chrono-unit :as chrono-unit])

(-> (ld/parse "1899-12-30")
;; => #object[java.time.LocalDate 0x499f935c "2020-11-17"]

(-> (ldt/parse "1899-12-30T00:00:00.000")
     (* 1e9 60 60 24 44152.33969907))
    (ldt/truncated-to chrono-unit/millis))
;; => #object[java.time.LocalDateTime 0x7e8f1e44 "2020-11-17T08:09:09.999"]