Convert date to unix timestamp in EXCEL

Because the project needs to import the original data to the new project, and the original data Time format is the excel date format, that is, the format of xxxx/xx, the time format of the new project is stored in unix timestamp. Therefore, you need to use the excel function to transfer the time.

The code is as follows:	Copy code
INT (B2-70 * 365-19) * 86400-8*3600)

B2 is the original date format xxxx/xx. The EXCEL time is calculated from January 1, January 1, 1900, while the unix time stamp is January 1, January 1, 1970, therefore, we need to subtract 70 * The number of days in January 1, 365, followed by-19 for the day's fine-tuning calculation. The previous number of days in a leap year, followed by 8*3600 is the time zone time difference, 86400 is the number of seconds in a day.

For example, after conversion on, the number will be changed to 1395849600, which is the timestamp in unix.

Supplement:

The UNIX timestamp 0 follows the ISO 8601 standard: 1970-01-01T00: 00: 00Z.

The UNIX timestamp format for one hour is 3600 seconds. The UNIX timestamp for one day is 86400 seconds. The leap second is not calculated.

In most UNIX systems, the UNIX timestamp is stored as 32-bit, which may cause the 2038 issue or Y2038.

Time	Seconds
1 minute	60
1 hour	3600
1 day	86400
1 week	604800
January (30.44 days)	2629743
1 year (365.24 days)	31556926