In this Excel Training video, we’ll teach you how to convert a UNIX timestamp to any date or time format using a date formula. UNIX timestamps are useful, as they are numbers that can be stored in a single cell.
In this video, we’ll show you how to create a UNIX timestamp in your Excel worksheet, and then convert it using a date formula to a readable date/time format.
Resources
Office Script:
function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet
let sheet = workbook.getActiveWorksheet();
// Get the current UNIX timestamp
let timestamp = Math.floor(Date.now() / 1000);
// Get the cell where you want to insert the timestamp
let cell = sheet.getRange("H1");
// Write the timestamp value to the cell
cell.setValue(timestamp);
}Formula to convert UNIX into date/time:
=TEXT(DATE(1970,1,1) + (C2 / 86400), "dd-mmm-yyyy hh:mm:ss")Time formats:
h – Hour without leading zero (0-23)
Example: 8 (for 8 AM)
hh – Hour with leading zero (00-23)
Example: 08 (for 8 AM)
m – Minute without leading zero (0-59)
Example: 7 (for 7 minutes past the hour)
mm – Minute with leading zero (00-59)
Example: 07 (for 7 minutes past the hour)
s – Second without leading zero (0-59)
Example: 9 (for 9 seconds past the minute)
ss – Second with leading zero (00-59)
Example: 09 (for 09 seconds past the minute)
AM/PM – Shows AM or PM
Example: 8PM (hAM/PM)
Example time format:
hh:mm:ss AM/PM
Example output:
08:30:25 AM
Date formats:
d: Day of the month without leading zero (1-31)
Example: 5 (for the 5th day of the month)
dd: Day of the month with leading zero (01-31)
Example: 05 (for the 5th day of the month)
ddd: Abbreviated day name (Sun, Mon, Tue, etc.)
Example: Wed (for Wednesday)
dddd: Full day name (Sunday, Monday, Tuesday, etc.)
Example: Thursday
m: Month without leading zero (1-12)
Example: 3 (for March)
mm: Month with leading zero (01-12)
Example: 03 (for March)
mmm: Abbreviated month name (Jan, Feb, Mar, etc.)
Example: Jul (for July)
mmmm: Full month name (January, February, March, etc.)
Example: September
mmmmm: Single letter month abbreviation (J, F, M, etc.)
Example: D (for December)
yy: Last two digits of the year (00-99)
Example: 21 (for the year 2021)
yyyy: Full year (e.g., 2021)
Example: 2023
Example date format:
ddd, mmmm dd, yyyy
Example output:
Thursday, August 10, 2023