Date and time

It is common to use Excel to create a time calculation tool or a date counter. However there are some limitations when it comes to conversion with Appizy. We have put together some practical samples on this page. You can find the original spreadsheet in the converter samples.

Delivery date: add days to a date

This tool calculates a delivery date from a purchase date and a delivery method entered by the user.

Building

The example here uses three dropdown lists to enter the date. This form of input is particularly suitable for mobile users. Moreover, it avoids the errors related to a text input (you can compare with the third tab).

The date entered is assembled with the DATE function of Excel which takes as parameters a year, a month and a day. The function VLOOKUP is used to convert the text month into a number.

Calculation of the sum

For our tool to work after conversion, we need to avoid mathematical operation with a date : the formula =B3+5 is valid in a spreadsheet, but it is not valid after conversion with Appizy.

To calculate this result in Appizy, we use the DATE formula, by getting the year, month and day data from cell B3. For a delivery date of D+5, we must indicate in cell B7 the following formula: =DATE(YEAR(B3),MONTH(B3),DAY(B3)+5).

The DATE function in cell B7 is coupled with a VLOOKUP function to select automatically the number of days according to the selected delivery mode.

Arrival time: time calculations

The tab Arrival time calculates the time to destination based on distance, speed and departure time.

This spreadsheet was created on the basis of two tabs:

• An interface visible to users, allowing data entry;
• A tab for the necessary calculations.

The logic of this spreadsheet is broken down into three parts :

Input data

Three data fields are presented, so that the user can fill in :

• His speed ;
• The distance covered;
• The time of departure: the proposed presentation is adapted to cell phones for a better use.

Calculation of the travel time

In order to calculate the arrival time, it is required to calculate the travel time based on the data entered by the user.

Travel time is equal to the following formula : distance / speed. However, the result must then be converted into hours and minutes. To do this we split the calculation in two steps:

• Calculation of hours: use the formula QUOTIENT ;
• Calculation of minutes: use the formula FLOOR.MATH and convert the remaining time into minutes using the multiple of 60.

The result is a travel time in “hours and minutes” format, which is displayed in the user interface.

Birthday: difference between two dates

This last tool calculates the number of days left before your next birthday.

This tool has 3 cells:

• The function TODAY to get the current date,
• A cell where you can enter the date of your next birthday. Remember to set a default date in this original spreadsheet to indicate that this cell is from type date,
• The calculation of the remaining days is done with the formula: =DATEDIF(B1,B2,"d")&" day(s)". This function DATEDIFF calculates the number of days between today’s date and the date filled. Note that this formula can be used in days, years or hours. You just have to modify the last parameter (“y”, “d”, “h”).