{"id":1054,"date":"2018-07-01T10:35:55","date_gmt":"2018-07-01T10:35:55","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=1054"},"modified":"2024-03-29T14:18:22","modified_gmt":"2024-03-29T14:18:22","slug":"timestamp","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/","title":{"rendered":"Timestamp in Excel"},"content":{"rendered":"\n<p>When you work with Excel, sometimes you may want to enter the current date or time near the list of items, such as expenses or investments. This operation is called timestamping. There are a few methods you can use to do it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">TODAY and NOW<\/h2>\n\n\n\n<p>These functions take no arguments. The\u00a0<strong>TODAY<\/strong>\u00a0function returns the current date and the\u00a0<strong>NOW<\/strong>\u00a0function returns both the date and time.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"336\" height=\"100\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png\" alt=\"\" class=\"wp-image-1056\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png 336w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now-300x89.png 300w\" sizes=\"(max-width: 336px) 100vw, 336px\" \/><\/figure>\n\n\n\n<p>The problem with these functions occurs when we use the&nbsp;<strong>F9<\/strong>&nbsp;key. Each time you do this the value is refreshed, so this may not be the best option for our example.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Keyboard shortcut<\/h2>\n\n\n\n<p>Another, even faster method is to use keyboard shortcuts. The first one is\u00a0<strong><span class=\"shortcut\">Ctrl + ;<\/span><\/strong>. You can use this one to insert the current date. The second one is\u00a0<strong><span class=\"shortcut\">Ctrl + Shift + ;<\/span><\/strong>. It can be used to insert the current time. Both shortcuts insert data as a number, so it won\u2019t change when you refresh the worksheet\u00a0<strong>(F9).<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"314\" height=\"100\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/keyboard-shortcuts.png\" alt=\"\" class=\"wp-image-1057\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/keyboard-shortcuts.png 314w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/keyboard-shortcuts-300x96.png 300w\" sizes=\"(max-width: 314px) 100vw, 314px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Circular reference<\/h2>\n\n\n\n<p>Instead of using keyboard shortcuts every time, a better way is to use a circular formula.<\/p>\n\n\n\n<p>A circular reference occurs when a formula refers to itself or another cell that it depends on.<\/p>\n\n\n\n<p>But before you use one of the following examples, you have to enable\u00a0<strong>iterative calculations<\/strong>. In order to do it, go to\u00a0<strong>FILE >> Options >> Formulas >> Calculation Options<\/strong>\u00a0and check to\u00a0<strong>Enable iterative calculation<\/strong>. Change\u00a0<strong>Maximum iterations<\/strong>\u00a0to\u00a0<strong>1<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"301\" height=\"143\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/iterative-calculation.png\" alt=\"\" class=\"wp-image-1058\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/iterative-calculation.png 301w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/iterative-calculation-300x143.png 300w\" sizes=\"(max-width: 301px) 100vw, 301px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Example 1:<\/h3>\n\n\n\n<p>The first example shows a formula that refers to itself.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">A1: =A1+1<\/pre>\n\n\n\n<p>Each time you refresh the worksheet&nbsp;<strong>(F9),<\/strong>&nbsp;the value is increased by&nbsp;<strong>1<\/strong>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example 2:<\/h3>\n\n\n\n<p>Cell&nbsp;<strong>A1<\/strong>&nbsp;refers to cell&nbsp;<strong>A2,<\/strong>&nbsp;but&nbsp;<strong>A2<\/strong>&nbsp;refers to&nbsp;<strong>A1.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">A1: = A2<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">A2: = A1+1<\/pre>\n\n\n\n<p>When you refresh the worksheet, cell&nbsp;<strong>A1<\/strong>&nbsp;will always be lower by&nbsp;<strong>1<\/strong>&nbsp;than&nbsp;<strong>A2.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Circular Timestamp<\/h2>\n\n\n\n<p>This formula will be used to generate our timestamp.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">A1: =IF(B1=\"\",\"\",IF(A1=\"\",NOW(),IF(A1=0, NOW(),A1)))<\/pre>\n\n\n\n<p>It may seem a little complicated. Let me explain.<\/p>\n\n\n\n<p><strong>Analysis:<\/strong><\/p>\n\n\n\n<ol>\n<li>If there is no <a href=\"https:\/\/officetuts.net\/excel\/formulas\/get-cell-value\/\">value in cell\u00a0<strong>B1<\/strong>\u00a0then<\/a> do nothing.<\/li>\n\n\n\n<li>If there is a value in cell&nbsp;<strong>B1,<\/strong>&nbsp;check if there is a value inside&nbsp;<strong>A1<\/strong>&nbsp;(current cell).<\/li>\n\n\n\n<li>If there is no value in cell\u00a0<strong>A1,<\/strong>\u00a0then insert the current date and time\u00a0<strong>(NOW).<\/strong><\/li>\n\n\n\n<li>If there is a value, but the value is\u00a0<strong>0<\/strong>, then insert\u00a0<strong>NOW.<\/strong><\/li>\n\n\n\n<li>If the value is different than&nbsp;<strong>0<\/strong>, keep the value&nbsp;<strong>(A1).<\/strong><\/li>\n<\/ol>\n\n\n\n<p>Enter the formula in cell&nbsp;<strong>A1<\/strong>&nbsp;and press&nbsp;<strong>Enter.<\/strong>&nbsp;Nothing happens. Now, inside cell&nbsp;<strong>B1<\/strong>&nbsp;insert some data and refresh the worksheet.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"279\" height=\"60\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/circular-function.png\" alt=\"\" class=\"wp-image-1059\"\/><\/figure>\n\n\n\n<p>In cell\u00a0<strong>A1,<\/strong>\u00a0instead of time and date, there is a number. It happens this way because Excel treats data and time as numbers. If you want to know more about it, read the lesson about\u00a0<a href=\"https:\/\/officetuts.net\/excel\/en\/training\/formatting-date-and-time\">dates and time formatting<\/a>\u00a0and another one about the method which Excel uses to\u00a0<a href=\"https:\/\/officetuts.net\/excel\/en\/training\/how-excel-stores-date-and-time\">store dates and times<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you work with Excel, sometimes you may want to enter the current date or time near the list of items, such as&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Timestamp in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to timestamp in Excel using functions TODAY and NOW, keyboard shortcuts, and circular formulas. Timestamping is an important tool for tracking expenses or investments.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Timestamp in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to timestamp in Excel using functions TODAY and NOW, keyboard shortcuts, and circular formulas. Timestamping is an important tool for tracking expenses or investments.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-01T10:35:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T14:18:22+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Timestamp in Excel\",\"datePublished\":\"2018-07-01T10:35:55+00:00\",\"dateModified\":\"2024-03-29T14:18:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\"},\"wordCount\":475,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png\",\"keywords\":[\"pinterest\"],\"articleSection\":[\"formulas\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\",\"url\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\",\"name\":\"Timestamp in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png\",\"datePublished\":\"2018-07-01T10:35:55+00:00\",\"dateModified\":\"2024-03-29T14:18:22+00:00\",\"description\":\"Learn how to timestamp in Excel using functions TODAY and NOW, keyboard shortcuts, and circular formulas. Timestamping is an important tool for tracking expenses or investments.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Timestamp in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Timestamp in Excel","description":"Learn how to timestamp in Excel using functions TODAY and NOW, keyboard shortcuts, and circular formulas. Timestamping is an important tool for tracking expenses or investments.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/","og_locale":"en_US","og_type":"article","og_title":"Timestamp in Excel","og_description":"Learn how to timestamp in Excel using functions TODAY and NOW, keyboard shortcuts, and circular formulas. Timestamping is an important tool for tracking expenses or investments.","og_url":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/","article_published_time":"2018-07-01T10:35:55+00:00","article_modified_time":"2024-03-29T14:18:22+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Timestamp in Excel","datePublished":"2018-07-01T10:35:55+00:00","dateModified":"2024-03-29T14:18:22+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/"},"wordCount":475,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png","keywords":["pinterest"],"articleSection":["formulas"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/","url":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/","name":"Timestamp in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png","datePublished":"2018-07-01T10:35:55+00:00","dateModified":"2024-03-29T14:18:22+00:00","description":"Learn how to timestamp in Excel using functions TODAY and NOW, keyboard shortcuts, and circular formulas. Timestamping is an important tool for tracking expenses or investments.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/formulas\/timestamp\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/07\/today-and-now.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/formulas\/timestamp\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Timestamp in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/1054"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=1054"}],"version-history":[{"count":6,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/1054\/revisions"}],"predecessor-version":[{"id":17396,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/1054\/revisions\/17396"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=1054"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=1054"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=1054"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}