Excel unixtime to date

The Bereznikers

Donate

Excel — Convert Unix Time to Excel Time

If you ever find yourself needing to convert from UNIX time in Excel, here’s the solution.

Unix time is the number of seconds since January 1, 1970.

Excel doesn’t contain built-in functions for working with Unix dates so they must be derived.
Excel allows you to add a number of days to a date by using the «+» operator. Let’s make use of that.
First convert the number of seconds to number of days (by dividing by 60*60*24) and then add the result to the date «1/1/1970».
The formula will look like

The quotes around the date are required. If they are not present, Excel will treat 1/1/1970 as an expression.

On Linux (or any other OS that uses coreutils), you can type

to see the current unix time.

Vadim Berezniker’s blog
Log in to post comments
621590 reads

Comments

Why are there several hundred examples on the web of converting unix time to excel time — and not a SINGLE one to convert the other way?

It’s easy

where CELL is the cell containing a regular date:

Can I calculate a whole column of timestamps to unix time?

Hey man. How do I convert a whole COLUMN of timestamps, to unix time in another column then? (a formula I place in the top of the column that calculates this whole column).

I have a huge XML file with thousands of timestamps, (listed in the format: «14-06-2009 16:21:01», «24-02-2007 19:25:10», etc.) that I want to convert to unix time.
Pasting a formula for EACH of these entries one at a time, would be death.

Google for ‘Excel how to copy formula’

Google for Excel how to copy formula

Hey, thanks for the tip

Hey, thanks for the tip. That did the job ; )

Does it matter which regular date format is used?

What format should be used for regular date format as there are several used? So, I just the regular date format into formula and it will convert to Unix time? Would it look like this? =(B1-«1/1/1970»)*60*60*24

Of course you have the right

Of course you have the right to complain that you didn’t pay attention in math lessons in school.

But what is so hard in transforming a simple math formular to the inverse computation?

Examples: an hour is 60 mintes, so minutes=60*hours. Now, how do you compute the hours, if you know the minutes?

An excellent example

Congratulations Vadim — this is the clearest example I have seen for this time conversion.
I rate this 5 out of 5 :-))

Well done

Hey thanks for the formula man.What a great site.Bravoo.

Re: Excel — Convert Unix Time to Excel Time

Thank You Vadim. the formula works just great.

Оценка: 6 по 5 и бальной шкале

Which regular date format to use?

Does not take the time zone into account

Be aware that Unix time is the number of seconds since January 1, 1970 for UTC/GMT while Excel uses the number of days and fractions of the day for the local time zone.

If you convert a value such as 1322164881 using =CELL/(60*60*24)+»1/1/1970″ you will get 2011-11-24 20:01:21 which is correct for UTC or the GMT time zone.

I’m in Pacific standard time time at the moment and expected to see 2011-11-24 12:01:21. Unfortunately, discovering the current time zone offset in Excel is a major pain. I found this page, http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx. The LocalOffsetFromGMT() function on that page works and returns the number of minutes from GMT meaning I divide that by 60 and get the fraction of the day Excel uses.

Even that is not perfect. For example, let’s say I have 1314627183 which translates to 2011-08-29 14:13:03 GMT. We were on daylight savings at the time time. My current GMT offset is 8 hours but during the summer it’s 7 hours and so 1314627183 should translate to 2011-08-29 07:13:03 for me. To add to the pain, a few years ago the USA changed the dates we switched to/from daylight savings time. In my case, I decided I did not care if a displayed time was off by an hour should the daylight savings mode be different between «now» and a given Unix time and so using «LocalOffsetFromGMT()/8» works for me.

Someone asked above «What format should be used for regular date format as there are several used?» You can use «1970-01-01» which Excel should translate reliably. If that fails then use =CELL/(60*60*24)+DATE(1970,1,1). As it is, it won’t matter if your local format is «d/m/yyyy» as «1/1/1970» is the same regardless of the month/day order.

Thanks for the info. For the

Thanks for the info. For the formatting in excel, if you want to see the date and time, use a custom cell format = m/d/yyyy h:mm:ss

Great post. Here’s a post

Great post. Here’s a post that shows you how to convert your excel to web in minutes http://blog.caspio.com/integration/convert-ms-excel-to-web/

Good post

I found this useful about a year ago and it has helped me again today.
Thanks!

Still useful 🙂

Alright, I’d like to bring my little stone to this matter :

«A1» being any cell.

This formula is a little bit refined : you got the date format in the same time 🙂

Читайте так же:

Как создать таблицу в word 2020

Thanks for your assistance

Thanks for your assistance with this formula — it was very helpful 🙂

Kudos to you!

Thanks

Thanks a ton.
It worked.

great post, thanks for share.

Convert date and time to UNIX format

I have to convert date (yyyy/mm/dd) with respective time of the day (hh/mm/ss) to UNIX time stamp in excel.
Please help me .

UNIX to Excel with TIMEZONE

You may add timezone to mix by change the 1/1/1970 date and adding time to it.

For instance for US Eastern time (NY) change the date to «12/29/1969 8:00:00 pm» or CELL/(60*60*24)+»12/29/1969 8:00:00 pm»

EST is -5 and because of daytime saving on spring it will be -4 so the timestamp is 4 hours before 1/1/1997. After daytime saving it will be -5 again.

On other time zone like +6 you may use «1/1/197 6:00:00 am»

This worked for me.

Returns a number

The formula =CELL/(60*60*24)+»1/1/1970″ returns a number. Any idea how to convert that number to DateTime. For example while converting the value 1403877600000 using the above formula it returns 16274152.33

Try DATE(1970,1,1) instead of

Try DATE(1970,1,1) instead of «1/1/1970». Also in case you’re not aware of it: the 13 digit unix timestamp contains extra precision that you’ll either need to drop or handle in some other way.

Format the cell as Date.

For 16-digit dates I had to do this

converting UTF-8 to date in Excel

I have a UTF-8 database extract where the date appears like this: 1.44003E+12. I need to convert this value to a date in Excel. I tried using =CELL/(60*60*24)+»1/1/1970″, (where my cell is A1), but I end up with this: 16700780.07. Please help! I have scoured the web and cannot find a solution that works. I am working on a Mac. Thank you.

Try this instead:

Sometimes the value coming out is not seconds, but milliseconds.

Epoch time Bulk Conversion in Excel

Hello ,
one questions everyone asks, is about how to Bulk Convert when you have Epoch time in an Excel sheet with thousands of Rows to convert.
following few steps would do the magic

For Example ,
I have a CDR downloaded from Cisco Call Manager . The Call originating time usually comes up on Column E
I want to read it in human readable format and I have 1000 Rows to convert.
If there is no other referencing or calculation running on the excel sheet, I can insert a new column after E , by right clicking on Letter F and selecting insert Column (But if there is referencing already running in complex excel sheet, I would add a new sheet as inserting column can mess up other formulas so please be sure)

Column F is now Blank Column, I would call it Readable Call Originating Time , this description would be on F1
actual data starts from E2 to E1000 that I need to convert and place it in F2 to F1000

1. use the prov > Where E:E is the Cell number of Excel cell , if I were to convert only one cell E1 to F1 , I would just put E1 but since I have 1000 to convert ,
I would use E:E (which plainly means what ever is the row on E , I am on same Row). if your Column is A and you want all of it converted and placed in B, then you would use A:A in the formula placed in cells B2 to B1000.

2. Copy the same formula to all the F Column cells from F2 to F1000 (this can be done by one left click on cell F2 , you will see F2 would have highlighted border and on the right bottom corner of cell you will see a square dot, left click on the right bottom corner dot of the cell and drag it down , once you reach 1000 release the left click)

3. Change the Column format by right clicking on F Column and selecting Format Cells (you will see the whole Column is selected) and select Custom and select dd/mm/yyyy hh:mm or what ever format you prefer.

you could do all in one go as well by adding all the cell formatting within the formula as suggested above , its your choice , once you get understanding of each step , you can do step three as step 1 by changing the format prior to filling data.

How to Quickly Insert Date and Timestamp in Excel

A timestamp is something you use when you want to track activities.

For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc.

This Tutorial Covers:

Let’s get started.

Keyboard Shortcut to Insert Date and Timestamp in Excel

If you have to insert the date and timestamp in few cells in Excel, doing it manually could be faster and more efficient.

Here is the keyboard shortcut to quickly enter current Date in Excel:

Control + : (hold the control key and press the colon key).

Here is how to use it:

Select the cell where you want to insert the timestamp.
Use the keyboard shortcut Control + :
- This would instantly insert the current date in the cell.

A couple of important things to know:

This shortcut would only insert the current date and not the time.
It comes in handy when you want to selectively enter current date.
It picks the current date from your system’s clock.
Once you have the date in the cell, you can apply any date format to it. Simply go to the ‘Number Format’ drop-down in the ribbon and select the date format you want.

Читайте так же:

Не вставляется текст в word

Note that this is not dynamic, which means that it will not refresh and change the next time you open the workbook. Once inserted, it remains as a static value in the cell.

While this shortcut does not insert the timestamp, you can use the following shortcut to do this:

Control + Shift + :

This would instantly insert the current time in the cell.

So if you want to have both date and timestamp, you can use two different cells, one for date and one for the timestamp.

Using TODAY and NOW Functions to Insert Date and Timestamps in Excel

In the above method using shortcuts, the date and timestamp inserted are static values and don’t update with the change in date and time.

If you want to update the current date and time every time a change is done in the workbook, you need to use Excel functions.

This could be the case when you have a report and you want the printed copy to reflect the last update time.

Insert Current Date Using TODAY Function

To insert the current date, simply enter =TODAY() in the cell where you want it.

Since all the dates and times are stored as numbers in Excel, make sure that the cell is formatted to display the result of the TODAY function in the date format.

Right-click on the cell and select ‘Format cells’.
In the Format Cells dialog box, select Date category in the Number tab.
Select the required date format (or you can simply go with the default one).
Click OK.

Note that this formula is volatile and would recalculate every time there is a change in the workbook.

Insert Date and Timestamp Using NOW Function

If you want the date and timestamp together in a cell, you can use the NOW function.

Again, since all the dates and time are stored as numbers in Excel, it is important to make sure that the cell is formatted to have the result of the NOW function displayed in the format that shows date as well as time.

Right-click on the cell and select ‘Format cells’.
In the Format Cells dialog box, select ‘Custom’ category in the Number tab.
In the Type field, enter dd-mm-yyyy hh:mm:ss
Click OK.

This would ensure that the result shows the date as well as the time.

Note that this formula is volatile and would recalculate every time there is a change in the workbook.

Circular References Trick to Automatically Insert Date and Timestamp in Excel

One of my readers Jim Meyer reached out to me with the below query.

This can be done using the keyboard shortcuts (as shown above in the tutorial). However, it is not automatic. With shortcuts, you’ll have to manually insert the date and timestamp in Excel.

To automatically insert the timestamp, there is a smart technique using circular references (thanks to Chandoo for this wonderful technique).

Let’s first understand what a circular reference means in Excel.

Suppose you have a value 1 in cell A1 and 2 in cell A2.

Now if you use the formula =A1+A2+A3 in cell A3, it will lead to a circular reference error. You may also see a prompt as shown below:

This happens as you are using the cell reference A3 in the calculation that is happening in A3.

Now, when circular reference error happens, there is a non-ending loop that starts and would have led to a stalled Excel program. But the smart folks in Excel development team made sure that when a circular reference is found, it is not calculated and the non-ending loop disaster is averted.

However, there is a mechanism where we can force Excel to at least try for a given number of times before giving up.

Now let’s see how we can use this to automatically get a date and timestamp in Excel (as shown below).

Note that as soon as I enter something in cells in column A, a timestamp appears in the adjacent cell in column B. However, if I change a value anywhere else, nothing happens.

Here are the steps to get this done:

Go to File –> Options.
In the Excel Options dialog box, select Formulas.
In the Calculated options, check the Enable iterative calculation option.
Go to cell B2 and enter the following formula:

Now when you enter anything in column A, a time stamp would automatically appear in column B in the cell adjacent to it.

With the above formula, once the timestamp is inserted, it doesn’t update when you change the contents of the adjacent cell.

If you want the timestamp to update every time the adjacent cell in Column A is updated, use the below formula (use Control + Shift + Enter instead of Enter):

This formula uses the CELL function to get the reference of the last edited cell, and if it’s the same as the one to the left of it, it updates the timestamp.

Note: When you enable iterative calculations in workbook once, it will be active until you turn it off. To turn it off, you need to go to Excel Options and uncheck the ‘Enable iterative calculation’ option.

Using VBA to Automatically Insert Timestamp in Excel

If VBA is your weapon of choice, you’ll find it to be a handy way to insert a timestamp in Excel.

VBA gives you a lot of flexibility in assigning conditions in which you want the timestamp to appear.

Below is a code which will insert a timestamp in column B whenever there is any entry/change in the cells in Column A.

This code uses the IF Then construct to check whether the cell that is being edited is in column A. If this is the case, then it inserts the timestamp in the adjacent cell in column B.

Читайте так же:

Толщина линий таблицы в word

Note that this code would overwrite any existing contents of the cells in column B. If you want. You can modify the code to add a message box to show a prompt in case there is any existing content.

Where to Put this Code?

This code needs to be entered as the worksheet change event so that it gets triggered whenever there is a change.

Right-click on the worksheet tab and select View Code (or use the keyboard shortcut Alt + F11 and then double click on the sheet name in the project explorer).
Copy paste this code into the code window for the sheet.
Close the VB Editor.

Make sure you save the file with .XLS or .XLSM extension as it contains a macro.

Creating a Custom Function to Insert Timestamp

Creating a custom function is a really smart way of inserting a timestamp in Excel.

It combines the power of VBA with functions, and you can use it like any other worksheet function.

Here is the code that will create a custom “Timestamp” function in Excel:

Where to Put this Code?

This code needs to be placed in a module in the VB Editor. Once you do that, the Timestamp function becomes available in the worksheet (just like any other regular function).

Here are the steps to place this code in a module:

Press ALT + F11 from your keyboard. It will open the VB Editor.
In the Project Explorer in VB Editor, right-click on any of the objects and go to Insert –> Module. This will insert a new module.
Copy paste the above code in the module code window.
Close the VB Editor or press ALT + F11 again to go back to the worksheet.

Now you can use the function in the worksheet. It will evaluate the cell to its left and insert the timestamp accordingly.

It also updates the timestamp whenever the entry is updated.

Make sure you save the file with .XLS or .XLSM extension as it contains VB code.

Hope you’ve found this tutorial useful.

Let me know your thoughts in the comments section.

You May Also Like the Following Excel Tutorials and Resources:

Unix time конвертер (Конвертер времени Unix онлайн)

Что такое Unix время или Unix эпоха (Unix epoch или Unix time или POSIX time или Unix timestamp) ?

UNIX-время или POSIX-время (англ. Unix time) — способ кодирования времени, принятый в UNIX и других POSIX-совместимых операционных системах.
Моментом начала отсчёта считается полночь (по UTC) с 31 декабря 1969 года на 1 января 1970, время с этого момента называют «эрой UNIX» (англ. Unix Epoch).
Время UNIX согласуется с UTC, в частности, при объявлении високосных секунд UTC соответствующие номера секунд повторяются.
Способ хранения времени в виде количества секунд очень удобно использовать при сравнении дат (с точностью до секунды), а также для хранения дат: при необходимости их можно преобразовать в любой удобочитаемый формат. Дата и время в этом формате также занимают очень мало места (4 или 8 байтов, в зависимости от размера машинного слова), поэтому его разумно использовать для хранения больших объёмов дат. Недостатки в производительности могут проявиться при очень частом обращении к элементам даты, вроде номера месяца и т. п. Но в большинстве случаев эффективнее хранить время в виде одной величины, а не набора полей.

Обычная дата(Human readable time)	Секунды
1 минута	60 секунд
1 час	3600 секунд
1 день	86400 секунд
1 неделя	604800 секунд
1 месяц (30.44 дней)	2629743 секунд
1 год (365.24 дней)	31556926 секунд

Конвертивание эпохи Unix в человекопонятную дату(human readable date)

Unix дата начала и конца года, месяца или дня

Перевод секунд в дни, часы и минуты

Как получить Unix время в.

Perl	time
PHP	time()
Ruby	Time.now (или Time.new ). Чтобы вывести: Time.now.to_i
Python	import time сначала, потом time.time()
Java	long epoch = System.currentTimeMillis()/1000;
Microsoft .NET C#	epoch = (DateTime.Now.ToUniversalTime().Ticks — 621355968000000000) / 10000000;
VBScript/ASP	DateDiff(«s», «01/01/1970 00:00:00», Now())
Erlang	calendar:datetime_to_gregorian_seconds(calendar:now_to_universal_time( now()))-719528243600.
MySQL	SELECT unix_timestamp(now())
PostgreSQL	SELECT extract(epoch FROM now());
SQL Server	SELECT DATEDIFF(s, ‘1970-01-01 00:00:00’, GETUTCDATE())
JavaScript	Math.round(new Date().getTime()/1000.0) getTime() возвращает время в миллисекундах.
Unix/Linux	date +%s
Другие OS	Командная строка: perl -e «print time» (Если Perl установлен на вашей системе)

Конвертирование даты в Unix время в.

PHP	mktime(часы, минуты, секунды, месяц, день, год)
Ruby	Time.local(год, месяц, день, часы, минуты, секунды, usec ) (или Time.gm для GMT/UTC вывода). Чтобы вывести добавьте .to_i
Python	import time сначала, потом int(time.mktime(time.strptime(‘2000-01-01 12:34:00’, ‘%Y-%m-%d %H:%M:%S’)))
Java	long epoch = new java.text.SimpleDateFormat («dd/MM/yyyy HH:mm:ss»).parse(«01/01/1970 01:00:00»);
VBScript/ASP	DateDiff(«s», «01/01/1970 00:00:00», поле даты)
MySQL	SELECT unix_timestamp(время) Формат времени: YYYY-MM-DD HH:MM:SS или YYMMDD или YYYYMMDD
PostgreSQL	SELECT extract(epoch FROM date(‘2000-01-01 12:34’)); С timestamp: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE ‘2001-02-16 20:38:40-08’); C интервалом: SELECT EXTRACT(EPOCH FROM INTERVAL ‘5 days 3 hours’);
SQL Server	SELECT DATEDIFF(s, ‘1970-01-01 00:00:00’, поле с датой)
Unix/Linux	date +%s -d»Jan 1, 1980 00:00:01″

Конвертирование Unix времеми в понятную дату(human readable date).

PHP	date(Формат, unix время);
Ruby	Time.at(unix время)
Python	import time сначала, потом time.strftime(«%a, %d %b %Y %H:%M:%S +0000», time.localtime(unix время)) Замените time.localtime на time.gmtime для GMT даты.
Java	String date = new java.text.SimpleDateFormat(«dd/MM/yyyy HH:mm:ss»).format(new java.util.Date (unix время*1000));
VBScript/ASP	DateAdd(«s», unix время, «01/01/1970 00:00:00»)
PostgreSQL	SELECT TIMESTAMP WITH TIME ZONE ‘epoch’ + unix время * INTERVAL ‘1 second’;
MySQL	from_unixtime(unix время, не обязательно, выходной формат) Стандартный формат выхода YYY-MM-DD HH:MM:SS
SQL Server	DATEADD(s, unix время, ‘1970-01-01 00:00:00’)
Microsoft Excel	=(A1 / 86400) + 25569 Результат будет в GMT зоне времени. Для других временных зон: =((A1 +/- разница аремени для зоны) / 86400) + 25569.
Linux	date -d @1190000000
Другие OS	Командная строка: perl -e «print scalar(localtime(unix время))» (Если установлен Perl) Замените ‘localtime’ на ‘gmtime’ для GMT/UTC зоны времени.

Для чего нужен инструмент «Unixtime конвертер»?

Читайте так же:

Создание гипертекстового документа в word

Данный инструмент, в первую очередь, будет полезен веб-мастерам, которые постоянно имеют дело с большими объемами дат или часто в своей работе обращаются к их элементам. С помощью инструмента «Unixtime конвертер» можно легко конвертировать Unix время в понятную для пользователя дату (и наоборот), узнать текущее Unix epoch время, а также получить Unix время в различных языках программирования, СУБД и операционных системах.

Что такое Unix время?

Эра Unix (Unix epoch) началась в ночь с 31 декабря 1969 года на 1 января 1970 года. Именно эту дату взяли за точку отсчета «компьютерного» времени, которое исчисляется в секундах и занимает очень мало места на диске – всего 4 или 8 байт. С помощью такого способа кодирования программисты могут «спрятать» любую дату в одно число, и легко конвертировать его обратно в понятный пользователям формат.

Unix время (еще его называют Unix time или POSIX time) удобно использовать в различных операционных системах и языках программирования, так как оно отображается в виде одной величины, а не определенного количества полей, занимающих место. К тому же, UNIX time полностью соответствует стандарту UTC (в том числе и в високосных годах) – в таком случае соответствующие значения секунд просто повторяются.

Пару слов о терминах.

Итак, Unix-временем (или POSIX-временем) считается количество секунд, которые прошли с полуночи 1 января 1970 года до настоящего времени.

Unix Timestamp (временная метка) – это «зафиксированное» время, иными словами – конкретная дата, запечатленная в числе.

UTC (Universal Coordinated Time) – это Всемирное координированное время, которое «фиксируется» на нулевом меридиане, и от которого ведется отсчет географических часовых поясов.

Насколько «долговечна» данная система?

Всего лишь через пару десятков лет, а именно 19 января 2038 года в 03:14:08 по UTC Unix time достигнет значения 2147483648, и компьютерные системы могут интерпретировать это число как отрицательное. Ключ к решению данной проблемы лежит в использовании 64-битной (вместо 32-битной) переменной для хранения времени. В таком случае, запаса числовых значений Unix time хватит человечеству еще на 292 миллиарда лет. Неплохо, правда?

Unix время – одно для всех

Если вы живете в Лондоне или Сан-Франциско, а ваши друзья – в Москве, то «сверить часы» можно по Unix time: эта система в данный момент времени едина для всего мира. Естественно, если время на серверах выставлено правильно. А с помощью инструмента «Unixtime конвертер» такая конвертация займет у вас доли секунды.

How to Quickly Insert Date and Timestamp in Excel

A timestamp is something you use when you want to track activities.

This Tutorial Covers:

Let’s get started.

Keyboard Shortcut to Insert Date and Timestamp in Excel

If you have to insert the date and timestamp in few cells in Excel, doing it manually could be faster and more efficient.

Here is the keyboard shortcut to quickly enter current Date in Excel:

Control + : (hold the control key and press the colon key).

Here is how to use it:

Select the cell where you want to insert the timestamp.
Use the keyboard shortcut Control + :
- This would instantly insert the current date in the cell.

A couple of important things to know:

This shortcut would only insert the current date and not the time.
It comes in handy when you want to selectively enter current date.
It picks the current date from your system’s clock.
Once you have the date in the cell, you can apply any date format to it. Simply go to the ‘Number Format’ drop-down in the ribbon and select the date format you want.

Note that this is not dynamic, which means that it will not refresh and change the next time you open the workbook. Once inserted, it remains as a static value in the cell.

While this shortcut does not insert the timestamp, you can use the following shortcut to do this:

Control + Shift + :

This would instantly insert the current time in the cell.

So if you want to have both date and timestamp, you can use two different cells, one for date and one for the timestamp.

Using TODAY and NOW Functions to Insert Date and Timestamps in Excel

In the above method using shortcuts, the date and timestamp inserted are static values and don’t update with the change in date and time.

If you want to update the current date and time every time a change is done in the workbook, you need to use Excel functions.

This could be the case when you have a report and you want the printed copy to reflect the last update time.

Insert Current Date Using TODAY Function

To insert the current date, simply enter =TODAY() in the cell where you want it.

Since all the dates and times are stored as numbers in Excel, make sure that the cell is formatted to display the result of the TODAY function in the date format.

Right-click on the cell and select ‘Format cells’.
In the Format Cells dialog box, select Date category in the Number tab.
Select the required date format (or you can simply go with the default one).
Click OK.

Note that this formula is volatile and would recalculate every time there is a change in the workbook.

Insert Date and Timestamp Using NOW Function

If you want the date and timestamp together in a cell, you can use the NOW function.

Right-click on the cell and select ‘Format cells’.
In the Format Cells dialog box, select ‘Custom’ category in the Number tab.
In the Type field, enter dd-mm-yyyy hh:mm:ss
Click OK.

Читайте так же:

Как изменить размер документа word

This would ensure that the result shows the date as well as the time.

Note that this formula is volatile and would recalculate every time there is a change in the workbook.

Circular References Trick to Automatically Insert Date and Timestamp in Excel

One of my readers Jim Meyer reached out to me with the below query.

This can be done using the keyboard shortcuts (as shown above in the tutorial). However, it is not automatic. With shortcuts, you’ll have to manually insert the date and timestamp in Excel.

To automatically insert the timestamp, there is a smart technique using circular references (thanks to Chandoo for this wonderful technique).

Let’s first understand what a circular reference means in Excel.

Suppose you have a value 1 in cell A1 and 2 in cell A2.

Now if you use the formula =A1+A2+A3 in cell A3, it will lead to a circular reference error. You may also see a prompt as shown below:

This happens as you are using the cell reference A3 in the calculation that is happening in A3.

However, there is a mechanism where we can force Excel to at least try for a given number of times before giving up.

Now let’s see how we can use this to automatically get a date and timestamp in Excel (as shown below).

Note that as soon as I enter something in cells in column A, a timestamp appears in the adjacent cell in column B. However, if I change a value anywhere else, nothing happens.

Here are the steps to get this done:

Go to File –> Options.
In the Excel Options dialog box, select Formulas.
In the Calculated options, check the Enable iterative calculation option.
Go to cell B2 and enter the following formula:

Now when you enter anything in column A, a time stamp would automatically appear in column B in the cell adjacent to it.

With the above formula, once the timestamp is inserted, it doesn’t update when you change the contents of the adjacent cell.

If you want the timestamp to update every time the adjacent cell in Column A is updated, use the below formula (use Control + Shift + Enter instead of Enter):

This formula uses the CELL function to get the reference of the last edited cell, and if it’s the same as the one to the left of it, it updates the timestamp.

Using VBA to Automatically Insert Timestamp in Excel

If VBA is your weapon of choice, you’ll find it to be a handy way to insert a timestamp in Excel.

VBA gives you a lot of flexibility in assigning conditions in which you want the timestamp to appear.

Below is a code which will insert a timestamp in column B whenever there is any entry/change in the cells in Column A.

This code uses the IF Then construct to check whether the cell that is being edited is in column A. If this is the case, then it inserts the timestamp in the adjacent cell in column B.

Note that this code would overwrite any existing contents of the cells in column B. If you want. You can modify the code to add a message box to show a prompt in case there is any existing content.

Where to Put this Code?

This code needs to be entered as the worksheet change event so that it gets triggered whenever there is a change.

Right-click on the worksheet tab and select View Code (or use the keyboard shortcut Alt + F11 and then double click on the sheet name in the project explorer).
Copy paste this code into the code window for the sheet.
Close the VB Editor.

Make sure you save the file with .XLS or .XLSM extension as it contains a macro.

Creating a Custom Function to Insert Timestamp

Creating a custom function is a really smart way of inserting a timestamp in Excel.

It combines the power of VBA with functions, and you can use it like any other worksheet function.

Here is the code that will create a custom “Timestamp” function in Excel:

Where to Put this Code?

This code needs to be placed in a module in the VB Editor. Once you do that, the Timestamp function becomes available in the worksheet (just like any other regular function).

Here are the steps to place this code in a module:

Press ALT + F11 from your keyboard. It will open the VB Editor.
In the Project Explorer in VB Editor, right-click on any of the objects and go to Insert –> Module. This will insert a new module.
Copy paste the above code in the module code window.
Close the VB Editor or press ALT + F11 again to go back to the worksheet.

Now you can use the function in the worksheet. It will evaluate the cell to its left and insert the timestamp accordingly.

It also updates the timestamp whenever the entry is updated.

Make sure you save the file with .XLS or .XLSM extension as it contains VB code.

Hope you’ve found this tutorial useful.

Let me know your thoughts in the comments section.

You May Also Like the Following Excel Tutorials and Resources:

голоса

Рейтинг статьи

Excel unixtime to date

The Bereznikers

Recent comments

Donate

Excel — Convert Unix Time to Excel Time

Comments

It’s easy

Can I calculate a whole column of timestamps to unix time?

Google for ‘Excel how to copy formula’

Hey, thanks for the tip

Does it matter which regular date format is used?

Of course you have the right

An excellent example

Well done

Re: Excel — Convert Unix Time to Excel Time

Which regular date format to use?

Does not take the time zone into account

Thanks for the info. For the

Great post. Here’s a post

Good post

Still useful 🙂

Thanks for your assistance

Kudos to you!

Thanks

great post, thanks for share.

Convert date and time to UNIX format

UNIX to Excel with TIMEZONE

Returns a number

Try DATE(1970,1,1) instead of

Format the cell as Date.

For 16-digit dates I had to do this

converting UTF-8 to date in Excel

Try this instead:

Epoch time Bulk Conversion in Excel

How to Quickly Insert Date and Timestamp in Excel

Keyboard Shortcut to Insert Date and Timestamp in Excel

Using TODAY and NOW Functions to Insert Date and Timestamps in Excel

Insert Current Date Using TODAY Function

Insert Date and Timestamp Using NOW Function

Circular References Trick to Automatically Insert Date and Timestamp in Excel

Using VBA to Automatically Insert Timestamp in Excel

Creating a Custom Function to Insert Timestamp

Unix time конвертер (Конвертер времени Unix онлайн)

How to Quickly Insert Date and Timestamp in Excel

Keyboard Shortcut to Insert Date and Timestamp in Excel

Using TODAY and NOW Functions to Insert Date and Timestamps in Excel

Insert Current Date Using TODAY Function

Insert Date and Timestamp Using NOW Function

Circular References Trick to Automatically Insert Date and Timestamp in Excel

Using VBA to Automatically Insert Timestamp in Excel

Creating a Custom Function to Insert Timestamp