Convert date to unix timestamp in EXCEL

Source: Internet
Author: User
Tags iso 8601

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.