Date and time

Differences between Excel and JavaScript

We share with you a secret : Excel stores dates as numbers! To be sure, we invite you to give a “Number” format to a date you have just entered. For example, the date 02/02/2021 will be 44,229 once displayed as “Number” (corresponding to the of days elapsed since January 1, 1900).

This numeric storage allows very practical arithmetic operations : in Excel you can compute the difference of two dates to obtain a number of days. 04/02/2021 - 02/02/2021 = 2 because 44231 - 44229 = 2. In the same way, it is possible to add a number of days to a date to calculate a date in the future.

Unfortunately, all these mathematical operations do not work once your application has been converted with Appizy. This is because our algorithm converts the dates in your spreadsheet into JavaScript Date objects. And in JavaScript arithmetic operations will cause errors. There are some workaround that we have grouped them in the example section.

Date input: text versus dropdowns

In a spreadsheet program, a date is entered as a string. Additionally, Excel or LibreOffice automatically detect that we wanted to enter a date.

In a mobile application it can be interesting to use a set of drop-downs to enter separately the day, the month and the year. This avoids a keyboard input. Take a few minutes to compare the use of our examples on your mobile. Which input mode seems the most natural to you? Today more than 50% of the internet traffic is on mobile.

Formatting

If Appizy is able to recognize and convert a cell formatted in Excel into a “Date” format, this conversion will nevertheless be automatically done according to the American date format : MM/DD/YYYY.

In order to allow the recognition of a European format according to the DD/MM/YYYY model, it is necessary to follow the following steps :

  • Select the cell in your Excel file;
  • In the cell format dialog, select “Other formats”.
  • A dialog box opens, you must then modify the “Local” formatting to select “English (UK)”. Then select the DD/MM/YYYY format (first in the list), which will allow Appizy to convert your cell correctly.