Converting Excel Dates to UNIX Timestamps and Vice Versa

In our previous article, we explored how to convert a UNIX timestamp to an Excel date. However, it’s also important to know how to convert an Excel date value to a UNIX formatted timestamp. This is particularly useful when working with data that contains both Excel dates and UNIX timestamps, or when you need to perform calculations that require the use of UNIX timestamps.

To convert an Excel date to a UNIX timestamp, you can use the following formula:

=UNIX(A2,”dd/mm/yy hh:mm:ss”)

This formula takes the value in cell A2, which is formatted as an Excel date with the custom format “dd/mm/yy hh:mm:ss”, and converts it to a UNIX timestamp. The result is displayed in cell B2, which is formatted as a number with 0 decimal places.

To understand how this formula works, let’s break it down:

* `UNIX(A2,”dd/mm/yy hh:mm:ss”)` takes the value in cell A2 and converts it to a UNIX timestamp using the specified format.

* The first argument to `UNIX()` is the value to be converted, which is the Excel date value in cell A2.

* The second argument to `UNIX()` is the format string, which specifies how the value should be converted. In this case, we’re using the format “dd/mm/yy hh:mm:ss” to convert the Excel date to a UNIX timestamp.

Here’s an example of how this formula works:

Suppose we have the following Excel date value in cell A2: “04/05/18 13:45:00″. When we apply the `UNIX()` function to this value using the formula `=UNIX(A2,”dd/mm/yy hh:mm:ss”)`, we get the following result:

1523197900

This is the UNIX timestamp equivalent of the Excel date value.

It’s important to note that the format string used in this formula is specific to the custom format “dd/mm/yy hh:mm:ss” that we defined for the Excel date column. If you use a different custom format, you’ll need to adjust the format string accordingly.

Converting UNIX Timestamps to Excel Dates

To convert a UNIX timestamp to an Excel date, we can use a similar formula:

=TEXT(B2,”dd/mm/yy hh:mm:ss”)

This formula takes the value in cell B2, which is a UNIX timestamp, and converts it to an Excel date using the specified format. The result is displayed in cell A2, which is formatted as an Excel date with the custom format “dd/mm/yy hh:mm:ss”.

Here’s an example of how this formula works:

Suppose we have the following UNIX timestamp value in cell B2: 1523197900. When we apply the `TEXT()` function to this value using the formula `=TEXT(B2,”dd/mm/yy hh:mm:ss”)`, we get the following result:

04/05/18 13:45:00

This is the Excel date equivalent of the UNIX timestamp value.

Again, it’s important to note that the format string used in this formula is specific to the custom format “dd/mm/yy hh:mm:ss” that we defined for the Excel date column. If you use a different custom format, you’ll need to adjust the format string accordingly.

In conclusion, converting between UNIX timestamps and Excel dates can be done easily using the `UNIX()` and `TEXT()` functions in Excel. By understanding how these formulas work and how to use them correctly, you can perform calculations and data manipulation tasks with ease, regardless of whether your data is in UNIX timestamp or Excel date format.