How To Change Date Format In Excel

How to change date format in excel. Firstly you can understand how Microsoft Excel stores dates and times, then you can understand how to change the date format, and convert dates to numbers in excel. Below you can follow our guide and steps easy to learn to change the date format in excel.

Before starting how you can change the date format in excel understand first what is the date in excel?

What are Dates in Excel?

In the excel sheet, all dates are stored as integers representing the number. We have explained with examples, like If you type a date like 1/1/2019 in the excel cell stored number display like 434660. I think you don’t understand this stored number. Excel start calculate the year 1st January 1900 which is their store number and 1st January 2019 is after 434660 days at 1st January 1900. That is the main thing in excel that shows this type of integer number.

What is time in Excel?

Times are stored in excel as Decimal. An example 24 hours is in one day. Excel store time 0 to .99999 in a day. So 0 is define 0 and 23:59:59 is .99999. If you type 6:00 AM in excel that will show as per time store ” .25″.

Now Time and Date together Excel stores as compare Decimal with Numbers. For example, if you type 1/1/2019 at 6:00 AM the combined numbers are “434660.25”.

• 1st January 2019 is = 434660
• 6:00 AM is = .25

How to convert Date to Number in Excel

1. Put the date in the cell if you want to covert.
2. After that Press Ctrl+1 Buttons together. Another way you can do, Select this cell or cells you want to convert to the date format, right-click the mouse, in the menu select the format cell option.
3. A new window will open on your screen in the Excel sheet.
4. In the window select the (Number) Tab. After selecting click “General” Category under the Number tab.
5. Towards the right-hand side, you can see under the “Sample” box some numbers are there.
6. If you click the “ok” button then your Date will convert the number.

Example Screenshot Below.

DATEVALUE and TIMEVALUE formula in Excel.

Another way you can use if you want to convert date to number and time to decimal number in Excel. There have some formulas.

• DATEVALUE:- You can use the DATEVALUE formula to convert date to number in excel. Write This formula =DATEVALUE(“1/1/2019”) then press Enter button.

Note:- Keep your date put in an Inverted coma.

• TIMEVALUE:- You can use the TIMEVALUE formula to convert TIME to decimal number in excel. Write This formula =TIMEEVALUE(“6:00 AM”) then press Enter button.

Example Screen shot below

DATEVALUE &TIMEVALUE

If you want to date and time convert in the same cell then write the formula =DATEVALUE(“1/1/2019”)&TIMEVALUE(“6:00”) and then press the enter.

Example Screenshot below

• Note:- In this formula between time and date use “&” because this sign divides Time and date sections in this formula.

How to quickly apply the Default change date format in Excel.

Microsoft Excel has a default change date format in excel. The long and short two types of default date format options are there. You can change Multiple cell dates.

Do follow the steps and Example quickly you can default change the date format in excel.

1. First, select the date cell or cells you want to change.
2. After that Press Ctrl+1 Buttons together. Another way you can do this, Select this cell or cells you want to convert to the date format, right-click the mouse, after open the menu selects the “format” cell option.
3. A new window will open on your screen in the Excel sheet.
4. In this window, menu clicks the “Date” Category under the Number tab.
5. Select the option and change the default time program.
6. You can use the keyboard shortcut option to default change the date in excel. Press (Ctrl+Shift+#)

Example Screenshot Below

How to change date format in excel

There have many different ways to change the date format in excel. Now below we explain another easy way to change the date format in excel.

1. Put the date in the cell if you want to covert.
2. After that Press Ctrl+1 Buttons together. Another way you can do, Select this cell or cells you want to convert the date format, right-click the mouse, and after opening the menu select the format cell option
3. A new window will open on your screen in the Excel sheet.
4. In the window select (Number) Tab. After selecting click the “Date” Category under the Number tab.
5. Towards the right-hand side, you can see a box under the “Type” Section different types of formats are there.
6. Then you select a format and the preview will see under the sample button box. After click “Ok” your Date format will be changed.

Example Screenshot Below

Creating custom date format in excel

If you want to change the date format in excel then you have to follow some formats in the excel sheet. The point is How we creating a custom date format in an excel sheet. If you want to create your own format to change the date in excel you can follow Below mention easy steps to create your own format to change the date in excel.

1. Select the cell that you want to formated.
2. Press Ctrl+1 Buttons together.
3. Open a window in the excel sheet.
4. In the window select (Number) Tab. Then click the “Custom” Category under the Number tab.
5. Towards the right-hand side, you can see a box under the “Type” Section different types of formats are there.
6. If you want to create your own then type your own format. Example (ddd, mmmm, dd, yyyy)
7. Click the “Ok” button and see your own format. Example result:- (Tue, January,01,2019)

Example Screenshot Below

Below some Codes, their Descriptions and Examples in custom format in excel.

The easiest way to change date format in excel you can select existing formats. Select the “date” category under the number section then follow the right-hand side there have some existing formats, select any format, and press “Ok”.

If you follow the above mention steps or guides you can easily change the date format in Excel.