As a CPA, I use Excel a lot at work. And sometimes I use it with node.js programming language for some automation. when using it with the programming language, one thing that bugged me is to handle dates in Excel.
If you are familiar with Excel like me, you may already know that a date in Excel is in fact a number. For example, a date like 2015–3–25 in a cell is originally a number 42,088 and translated or calculated into a text such as ‘2015–3–25", “March, 25, 2015", “2015/03/25", or “3/25/2015". Why?
It is obvious why Excel is designed that way. There are various ways of expressing a date such as “2015–3–25”, “2015–03–25”, “2015/3/25”, “March, 25, 2015”, “2015/03/25” and on and on. Instead of making the computer recognize all of the date formats, which can be more than one hundred, it is much efficient to make one-to-one relationship between a number and a date.
For example, 42,088 is mapped with “2015–3–25” or “2015–3–25”, “2015–03–25”, “2015/3/25”, “March, 25, 2015”, “2015/03/25” and other various date formats pointing to the same date.
Then, where does the number, 42,088, come from? Of course, it is not a random number. It is the number of days passed after 1900–1–1. Thus it is like 1 mapped to 1900–1–1, and 2 mapped to 1900–1–2 , ….., and 42088 mapped to 2015–3–25, and 42,089 mapped 2015–3–26.
Some of you might notice Excel has borrowed the concept from the Unix-timestamps. This is what Excels official documentation says about the number
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
Since I have programmatically dealt with Excel a lot, I encounter cases where I need to switch dates in a string format to/from JavaScript Date object, and add several days to another day. I decided to create a set of functions that deal with Excel dates and put them on github and npmjs so that I can share it with others and make them sharable across my different projects.
Let me share the code of functions. For your information, excelDate means a number mapped with a date, and jsonData is JavaScript Date object/instance.
- The function excelDateToJsDate takes an Excel number such as 42088 and returns the corresponding JavaScript Date instance.
- The function jsDateToExcelDate takes JavaScript Date instance and returns Excel date as a number like 42088. It is inverse of the function excelDateToJsDate. For example, it takes a JavaScript Date instance “new Date(2015,2,25)” and returns 42088. Note that the month of a date instance starts from 0, which means January is represented by 0, not by 1. Thus, March is 2, not 3.
3. The function excelDateToStringDateFormat takes a number and returns a date in a string format of user’ choice.
4. The function addDate takes a date in yyyymmdd-formatted-string and returns a date in a string format of user’s choice. For example, if your parameter is “20200131”, 2, and “yyyy-mm-dd”, it will return 2020–2–2.
As of writing this post, the version is 1.0.7. I have put this up in my npm and github repository(https://github.com/tofusoup429/excel-date-handlers).
You can clone the codes by,
git clone https://githhub.com/tofusoup429/excel-date-handlersYou can install the module using npm,
npm install @tofusoup429/excel-date-handlersand use the module by importing like
import {excelDateToJsDate, jsDateToExcelDate, excelDateToStringDateFormat, addDates } from '@tofusoup429/excel-date-handlers'As time goes on, I would be updating the version and include more functions.
Thank you for reading. I hope this can be helpful to you.
