Create UNIX Timestamps with this Secret Formula

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

Resources

Recent Posts