You are currently viewing How to store dates in Excel

How to store dates in Excel

  • Post author:
  • Post category:Tips
  • Post comments:0 Comments

With the increased arithmetic functions of excel, it's important to know how dates are stored since it appears in most excel tasks. Excel normally stores dates in form of numbers referred to as date -serial numbers to help during calculations. A date is usually given a numerical value starting from 1/1/1900 in excel. This is because the date calendar in excel starts from 1/1/1900. In excel, the date 1/1/1900 is given a numeric value of 1, second January 1900 is given a value of 2. Additionally, the preferred format of storing dates is; year, month, day in separate columns or year and day-of-year in separate columns. Dates and time go hand in hand and timeshares are a common format when being stored in excel. They are stored as numerical values.

Let's look at the following steps guide in formatting dates

1. Select cells to be formatted

2. Press CTRL+1

3. In the format cells box, click the number tab

4. In the category list, click Date

5. Under Type, pick a date format. The format will preview in the sample box with the first date in your data.

Choose the language in Locale(location) to use a date format that displays dates in another language.

Alternatively, you can use the concatenate function as outlined below

1. Select a blank cell to output the concatenation result and enter the formula =CONCATENATE(TEXT(A2,"yyyy-mm-dd")",", B2

(Whereby A2 is the cell with the date you will concatenate, and B2 is another cell you will concatenate into it). Then press enter key.

2. Keep selecting the concatenation result cell, drag its autofill handle to the range as you need. You will then see the specified cells are concatenated into one cell with keeping the date format.

Storing dates using kutools for excel.

You can also use kutools for excel in storing dates as follows:

1. Select the range you will concatenate, then click kutools>merge and split>combine rows, columns, or cells without losing data.

2. In the opening, combine columns or rows dialogue box as follows:

3. Check the combine column option on the to combine selected cell according to the following option selection.

2. Check one separator option in the specify a separator section.

3. Specify the place to put the concatenation results.

4. In the options section check the Delete contents of the combined cells option.

5. Check the use formatted values option

6. click ok to apply the utility. You will be able to see each row concatenated into its first cell while keeping all kinds of data formats and number formats.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.