Can't find what you're looking for?

Contact us or post in our forums and we'll help you find an answer.

Set formula to convert an integer to date in JavaScript | SpreadJS

Background:

To convert an integer to a date, set the following formula using SpreadJS: “DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))”

This formula will extract the year by taking the last 4 digits: RIGHT(A1,4)

Extract the month by getting the 3rd and 4th digit using the function MID like so: MID(A1, 3, 2)

To extract the day, us the LEFT function to return the first 2 characters of the integer: LEFT(A1,4)

Steps to Complete:

1. Set 8-digit integer to cell A1

2. Set formula to cell A2 and have the formula point to cell A1

Getting Started:

Step 1: Set 8-digit integer to cell A1

Set the 8-digit integer, 20200617, to cell A1 to be converted to a date format

        // integer to be converted in cell A1
        sheet.setValue(0, 0, 20200617);

Step 2: Set formula to cell A2 and have the formula point to cell A1

Set the formula, =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), to the cell A2 to take the integer in cell A1 and convert it to a date format

        // set formula to convert int to date in cell B1
        sheet.setFormula(0, 1, "=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))");