EXCEL HACKING: Secret formula to create UNIX timestamps and convert them effortlessly

In Excel, you can convert a UNIX timestamp to any date or time format you want 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 date/time format.

👋 Hi! Was this video helpful?

If so, consider buying us a coffee. We'd really appreciate it, and you'd be helping us grow the channel! 🥰

☕️ Buy us a coffee:

Thank you! ❤️

*************
💻 Extra resources:

Office Script:

function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet

// Get the current UNIX timestamp

// Get the cell where you want to insert the timestamp

// Write the timestamp value to the cell
}

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

💬 Did you enjoy this video? Let us know all about it in the comments below!

Thank you very much for stopping by, we have lots of other great videos that can help you in a huge assortment of tech-related ways!

*************
🛒 Support BTP with your M365 purchases:

➡️ As full disclosure, Business Tech Planet uses affiliate links — such as the one above. Purchasing through these links gives Business Tech Planet a small commission at no extra cost to yourself, and it helps us continue to produce content for this channel.

*************
💯 Welcome to the official YouTube Channel of Business Tech Planet - We make business tech simple!

On this Channel, we explain how technology works and how your business can use it in terms that anyone can understand, regardless of technical knowledge. The way we do that is by offering incredibly thorough and easy-to-follow video tutorials that anyone can understand.
➡️ Here we explain how you can safeguard your business as you begin using more future-proof cloud-based tech, from explaining how you can improve security and your organization's redundancy.

We cater to audiences that may or may not be tech-savvy. The goal is to make sure our content is understandable to people from all walks of life as well as instructive on how to make the most of their business's technology.
➡️ We also operate a website that offers Tech Tips and guides related to issues people may have with Technology. The guides and tips we offer are done with the philosophy of providing excellent information for businesses regarding safety, future-proofing, and redundancies.

✅ Visit Business Tech Planet online:

So, if you are also passionate about making tech make sense, we upload video content frequently, and you should definitely subscribe to the channel and activation the bell icon to never miss a video! 🔔

*************
🕐 Timestamps:

0:00 - Intro
0:13 - What is a UNIX timestamp?
0:35 - Make a UNIX timestamp
2:43 - Convert a UNIX timestamp using a date formula
3:40 - Outro

*************
#BusinessTechPlanet #excel #exceltips #exceltricks #exceltutorial #microsoft365 #office365 #unix #time #date #formula