Dates : Image Credit Unsplash.comThere are a few ways to input dates into a spreadsheet. One, you can type the date you require in the format you like. Two, you can type a formula that will automatically update every day for you. Three, you can use a code that will input the date you are working on and this will not change. I will explain all three here in my tip. I should start by saying that I am using the English way of displaying dates which is:- day /month/ year. Not the US way which is month/ day / year

1. Typing in a date.

You can type a date into a cell of your spreadsheet in the format you require or change the format after you have input the date.

  • Type a date in one of these ways.




These will display like this. different dates

Dates are numbers but in a date format. Numbers all align on the right of the cell.

Excel understands when you type a number followed by another that fit the parameters of a date and do not start with an equal sign that you intended to type a date.

You can re-format these dates to any you require.

I personally would never format a date to this format. 1.1.19, as this could be misunderstood by someone else updating your spreadsheet and they could type the same format not by using the formatting tool. This will result in the date being changed by Excel into a character set not even a number as there are two decimal symbols therefore nullifying the date.

Here are the default layouts of dates on and English UK computer. Format Cells Dialog Box

2. Using a code for a date.

Type the following formula. “=today()”

This will change what you see in the cell to the date you are working in. Today’s date! This is a special code that will update to the present date every time you open that file, automatically. (As long as your calculation is set to the default of automatic.) This is found on the Formula tab all the way at the end on the right. Formulas Tab

To change the setting of Calculation

Select the calculation options tool. Calculations Options ToolA list appears. Calculation ListIf the tick is not next to the word Automatic. It means that any calculation you may have in the spreadsheet you are using will not update if you make changes to the data. Manual is a useful setting if you have a very large spreadsheet that has many calculations in a chain thus slowing down the computers reaction to you trying to input the next piece of data.

If you use the formula “=now()” this will result in not only current date but also the current time. This too will automatically update every time you open the file or every time you do a new calculation in that spreadsheet.

3. A static date.

This code is lovely if you just want to put in the current date or time and you’re not sure of what it is.

Type the following.

  • For a date. Hold down the CTRL key and type the semicolon.
  • For the time. Hold down the CTRL and the SHIFT keys and type the colon.

The results are these. Result 1These will NOT update unless you retype the code.

I have given you three different ways to create dates in Excel. Use the ones that give you the results you require.


Formatting in cells of Excel – 3 of 8



Tip – Using Functions in Excel



Please enter your comment!
Please enter your name here