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 investment. This operation is called timestamping. There are a few methods you can use to do it.

Contents

## TODAY and NOW

These functions take no arguments. The **TODAY** function returns the current date and the **NOW** function returns both date and time.

The problem with these functions occurs when we use the **F9** key. Each time you do this the value is refreshed, so this may not be the best option for our example.

## Keyboard shortcut

Another, an even faster method is to use keyboard shortcuts. The first one is **Ctrl + ;**. You can use this one to insert the current date. The second one is **Ctrl + Shift + ;**. It can be used to insert the current time. Both shortcuts insert data as a number, so it won’t change when you refresh the worksheet **(F9).**

## Circular reference

Instead of using keyboard shortcuts every time, a better way is to use a circular formula.

A circular reference occurs when a formula refers to itself or another cell that it depends on.

But before you use one of the following examples, you have to enable **iterative calculations**. In order to do it, go to **FILE >> Options >> Formulas >> Calculation Options** and check **Enable iterative calculation**. Change **Maximum iterations** to **1**.

### Example 1:

The first example shows a formula which refers to itself.

A1: =A1+1

Each time you refresh the worksheet **(F9),** the value is increased by **1**.

### Example 2:

Cell **A1** refers to cell **A2,** but **A2** refers to **A1.**

A1: = A2

A2: = A1+1

When you refresh the worksheet, cell **A1** will always be lower by **1** than **A2.**

## Circular Timestamp

This formula will be used to generate our timestamp.

A1: =IF(B1="","",IF(A1="",NOW(),IF(A1=0, NOW(),A1)))

It may seem a little complicated. Let me explain.

**Analysis:**

- If there is no value in cell
**B1**than do nothing. - If there is a value in cell
**B1,**check if there is a value inside**A1**(current cell). - In there is no value in cell
**A1,**then insert current date and time**(NOW).** - If there is value, but the value is
**0**, then insert**NOW.** - If the value is different than
**0**, keep the value**(A1).**

Enter the formula in cell **A1** and press **Enter.** Nothing happens. Now, inside cell **B1** insert some data and refresh the worksheet.

In cell **A1,** instead 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 dates and time formatting and another one about the method which Excel uses to store dates and time.