How To Convert Timestamp To Date In Excel (5 Suitable Ways)

When we work with raw data that are exported from many sources like databases, log files, or APIs we often get the output in timestamps. This is not what we want because timestamps are hard to read and interpret. Most Excel users usually need to convert these timestamps into human readable dates so that they can perform sorting, filtering, or reporting.

What Is Timestamp in Excel?

A timestamp is a numeric or string value that represents a specific point in time. In most systems, Unix timestamps are commonly used and represent the number of seconds (or milliseconds) since January 1, 1970 (UTC). Excel needs these timestamps to be converted into its own date system to display meaningful calendar dates.

This method is used when timestamps are stored in text format like YYYYMMDDhhmmss. It is helpful for logging from a device or database where each record includes both date and time in one long string. This method works well if you want to isolate the date portion in Excel. Here we have timestamp data exported from a security system or automated logging system. Each timestamp follows the structured format YYYYMMDDhhmmss (14 digits, no milliseconds), and we want to extract the date from it.

Steps:

➤ Open Excel file that contains the timestamp strings. Suppose our data is in column B, starting from cell B2.

➤ In cell C2, enter the following formula:

Here,

• LEFT(B2,4) → extracts the year
• MID(B2,5,2) → extracts the month
• MID(B2,7,2) → extracts the day
• MID(B2,9,2) → extracts the hour
• MID(B2,11,2) → extracts the minute
• MID(B2,13,2) → extracts the second
• DATE(…) + TIME(…) → combines date and time into a complete Excel serial number

➤ After entering the formula, press Enter. You will see the full date displayed in standard Excel format (e.g., 5/12/2025).

➤ Drag the fill handle (small square at the bottom-right corner of cell C2) down to fill the formula for all rows in your dataset.

This method is used when timestamps are stored as Unix time, the number of seconds since January 1, 1970. These are commonly found in server logs, APIs, mobile data exports, and some CRM databases. You can convert them into readable Excel date-time values using a formula.

Steps:

➤ Open your Excel spreadsheet containing Unix timestamps. Here the Unix timestamps are listed in column B, starting from cell B2.

➤ Type the following formula in C2:

Here,

• B2/60 → converts seconds to minutes
• /60 again → converts minutes to hours
• /24 → converts hours to days
• + DATE(1970,1,1) → adds the number of days to the Unix epoch start date

➤ After typing the formula, press Enter. You will see a numeric value or a date-time depending on formatting. If it appears as a number like 45439.5, don’t worry as this is Excel’s internal date-time format.

➤ To display the result as a readable date-time, Select the result cell (C2). Then go to the Home tab. In the Number group, click on the drop-down and choose Short Date.

➤ Use the fill handle (small square at the corner of C2) to drag the formula down for all rows that contain Unix timestamps.

This method is used to convert Unix timestamps (seconds since 1-Jan-1970) into readable date-time values in Excel by using the Excel base date system. Instead of the DATE function, this method uses the base serial number 25569, which corresponds to 1-Jan-1970 in Excel’s serial date format.

Steps:

➤ Open your Excel file containing the Unix timestamps.

➤ Type the following formula in cell C2:

Here,

• B2/86400: Converts the Unix time from seconds into days (since there are 86,400 seconds in a day)
• + 25569: Adds the Excel serial number equivalent for 1-Jan-1970

➤ Press Enter.

➤ Drag the fill handle down to apply the formula and formatting to the entire column.

➤ Select Short Date to format as standard date.

➤ Here is the final result.

This method is for converting Unix timestamps that are recorded in tenths of seconds (11-digit values) into a readable date-time format in Excel. This format is sometimes used by high-resolution time logging systems. The approach divides the timestamp by 864000 (seconds × 10 per day) and adds DATE(1970,1,1) to align it with Excel’s serial date system.

Steps:

➤ Open the Excel file that contains the 11-digit Unix timestamps. Here, the timestamps are in column B, starting from cell B2.

➤ Enter the following formula in cell C2:

Here,

• 864000 = 86,400 seconds per day × 10 (because the value is in tenths of a second)
• DATE(1970,1,1) sets the origin date from which Unix time is calculated

➤ Press Enter. The result may show a numeric value (e.g., 455441) representing an Excel serial date.

➤ Convert the serial number into a readable format:

➤ Use the fill handle to drag down from cell C2 to the rest of the column.

This method uses a custom VBA function to convert Unix timestamps (seconds since 1-Jan-1970) into Excel-readable date-time format. Use this when you have datasets that include raw Unix timestamps.

Steps:

➤ Open your Excel datasheet and press  ALT  +  F11  to open the Visual Basic for Applications (VBA) editor.

➤ Go to the menu → Insert → Module

➤ Paste the following VBA function into the module:

Function UnixToExcel(UnixTime As Double) As Date
    UnixToExcel = DateAdd("s", UnixTime, "1/1/1970")
End Function

Here,

• UnixTime is the input in seconds.
• DateAdd(“s”, UnixTime, “1/1/1970”) adds the Unix seconds to Excel’s base date.

➤ Close the VBA editor.
➤ In cell C2, enter:

➤ Click Enter

➤ Format the result as a readable date-time and drag the formula down to apply it to all rows.

Frequently Asked Questions (FAQs)

How to convert timestamp into date in Excel?

Use the formula =A1/86400 + DATE(1970,1,1) for Unix timestamps in seconds. Format the result cell as a Date.

How to convert 13 digit timestamp to date in Excel?

Divide the 13-digit timestamp by 1000 to get seconds, then use:
=A1/86400 + DATE(1970,1,1)

Can we convert date to timestamp?

Yes. Use: =(A1 – DATE(1970,1,1)) * 86400 to convert a date to Unix timestamp (in seconds).

How do I get a date timestamp in Excel?

Use the formula =NOW() to insert the current date and time. Or use =TODAY() for date only.

Concluding Words

Converting timestamps to readable dates in Excel is easy once you know which method to apply. In this article, we describe multiple methods. You can focus on the formula that is suitable for Unix-based timestamps. For millisecond or formatted text timestamps, you just need slight adjustments to help achieve the same.