date to Excel date converter

[Marcin O]

Dear forum,

 

I wish to convert date in a format 2025-05-15 into Excel convention so amount of days since 1 January 1900 (45792). Did anyone try to solve this problem? 

 

for example this code below, but maybe there is better solution, much simpler, or by using standard format inside AutoLISP.

 

Thanks in advance

 

M

(defun datetoexcel (y m d / days)
  (setq days (+ (* 365 (- y 1900)) (/ (- y 1901) 4) d))
  (if (> m 1) (setq days (+ days 31)))
  (if (> m 2) (setq days (+ days (if (= 0 (rem y 4)) 29 28))))
  (if (> m 3) (setq days (+ days 31)))
  (if (> m 4) (setq days (+ days 30)))
  (if (> m 5) (setq days (+ days 31)))
  (if (> m 6) (setq days (+ days 30)))
  (if (> m 7) (setq days (+ days 31)))
  (if (> m 8) (setq days (+ days 31)))
  (if (> m 9) (setq days (+ days 30)))
  (if (> m 10) (setq days (+ days 31)))
  (if (> m 11) (setq days (+ days 30)))
  (+ days 1))

 

Edited May 23 by Marcin O
better title

[GLAVCVS]

Hi
I haven't been able to get your code to work properly.
Also, I don't think it takes leap years into account.
Actually, 45,798 days have passed, but not 45,792.

 

I have something pretty simple for this.
I hope it helps.

;************************ G L A V C V S *************************
;************************** F E C I T ***************************
(defun daysFrom1900 (dia mes annvs / aa diasHAP diasB diasBs da m-1 n)
  (defun diasBs (ai af i / a b)
    (while (<= (setq a (+ ai (setq i (1+ i)))) af)
      (if (or (= (rem a 400) 0) (and (= (rem a 4) 0) (/= (rem a 100) 0))); (setq a 1900) (or (= (rem a 400) 0) (and (= (rem a 4) 0) (/= (rem a 100) 0)))
	(setq b (if b (1+ b) 1))
      )
    )
    (if b b 0)
  )
  (setq	diasHAP (* (- annvs 1900) 365)
	diasB (diasBs 1900 (- annvs 1) -1)
	da (- (* (setq m-1 (- mes 1)) 31) (foreach x '(4 6 9 11)  (if (> mes x) (setq n x) (if n n 0))));(vl-some '(lambda (x / n) (if (> m-1 x) (not (setq n (if n (1+ n) 1))) n)) '(4 6 9 11)))
	da (if (and (> mes 2) (or (= (rem annvs 400) 0) (and (= (rem annvs 4) 0) (/= (rem annvs 100) 0)))) (- da 2) (if (> mes 2) (- da 3) da))
  )
  (+ diasHAP diasB da dia)
)

 

Edited May 24 by GLAVCVS

[GLAVCVS]

I've only changed the function name for you.
If the order of the dd/mm/yyyy arguments is a problem for you, you'll need to change them.

[Lee Mac]

Here's another way -

(defun ctoe ( y m d )
    (if (not ctoj) (load "julian.lsp" nil))
    (if ctoj (- (ctoj y m d 0 0 0) 2415019))
)

 

Edited May 24 by Lee Mac

[Marcin O]

@Lee Mac many thanks for simple script - wrapper for ctoj function. I did not know there is a julian.lsp Julian calendar included in Express tools  

@GLAVCVS thank You for another option for calculations