This post will guide you how to convert a date string to a Unix time stamp in Excel. How do I convert excel date to Unix timestamp with a formula in Excel. How to convert Unix time stamp to an excel date string in Excel.

1. Convert Excel Date to Unix Time Stamp

If you want to convert the excel date string to unix time stamp in Excel, you can create a formula based on the DATE function.

For Example, you have a list of data that contain dates, and you want to convert each date to unix time stamp. You can use the following formula.

=(A1-DATE(1970,1,1))*86400

Type this formula into a blank cell and press Enter key and then drag the AutoFill handle from Cell B1 to B5.

2. Converting Excel Date to Unix Time Stamp using VBA

Now, for those who require more control over the conversion process, we have the VBA code method. By writing a custom VBA script, you can create a macro that not only automates the conversion of Excel dates to Unix Time Stamps but also integrates seamlessly into your existing workflows. This method is perfect for users who deal with large datasets or need to perform the conversion repeatedly.

Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Insert a new module if one doesn’t exist already.

Copy and paste the following VBA code into the module:

Function ExcelToUnix(ByVal dt As Date) As Long
    ExcelToUnix = (dt - DateSerial(1970, 1, 1)) * 86400
End Function

Close the VBA editor and return to the Excel workbook.

In a blank cell, enter the formula:

=ExcelToUnix(A1)

Replace “A1” with the cell reference containing the Excel date.

Press Enter to apply the formula.

The cell will display the Unix time stamp corresponding to the Excel date.

3. Convert Unix Time Stamp to Excel Date

To convert a unix time stamp to an excel date string in Excel, you can use a formula based on the Date function. Like this:

=(((B1/60)/60)/24)+DATE(1970,1,1)

Type this formula into a cell and press Enter key, and then drag the AutoFill Handle from Cell C1 to C5.

Then you need to select the range of cells C1:C5, and right click on it, and select Format Cell from the popup menu list. And the Format Cell dialog will open. Click Date under Category list box, and then click OK button.

Let’s see the result:

4. Convert Unix Time Stamp to Excel Date using VBA

Next, let’s explore the second method using VBA code to convert Unix time stamps to Excel dates.

Press ALT + F11 to open the Visual Basic for Applications editor.

Insert a new module if one doesn’t exist already.

Copy and paste the following VBA code into the module:

Function UnixToExcel(ByVal ts As Long) As Date
    UnixToExcel = DateAdd("s", ts, "1970-01-01 00:00:00")
End Function

Close the VBA editor and return to the Excel workbook.

In a blank cell, enter the formula:

=UnixToExcel(A1)

Replace “A1” with the cell reference containing the Unix time stamp.

Press Enter to apply the formula.

The cell will display the corresponding Excel date for the Unix time stamp.

5. Video: Convert Excel Date to Unix Time Stamp

This Excel video tutorial, where we’ll delve into two methods for seamlessly converting Excel dates to Unix time stamps and vice versa. In the first method, we’ll utilize a formula, while the second method involves leveraging VBA code.

https://youtu.be/4KoLr9LgfEk

6. Related Functions

• Excel DATE function
  The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…