3 ways to format dates in Excel you should pocket right away

How to format dates in Excel is one of the basic Excel tips and tricks that everyone should refer to, especially office workers. Below article, GhienCongListen will guide 3 how to format date in excel very simple.

How to format dates in Excel

How to quickly enter dates in Excel

To do a quick date entry in excel, do the following:

Advertisement

Step 1: Select the date you want to format.

Step 2: In tab Homeopen the selection list Generalselect the small arrow next to the box > select the desired format (including Short Date or Long Date to select the appropriate date format).

Advertisement

Quick Tip: If you want to quickly do how to format dates into dd/mm/yyyy format in excel. Let’s use combination Ctrl + Shift + #then set the date you want.

Advertisement

Format the date in Excel with Format Cell

You can also do the formatting of dates in Excel using Format Cell.

Step 1: Select the blank cells where you want to change the format or the blank cells where you want to insert the date.

Step 2: Press Ctrl + 1 to open the Format Cell dialog box.

Step 3: Switch to tab Number and choose Date in the Category list.

Step 4: Here, select a desired date format.

Step 5: Select OK to save the format change and close the window.

Format dates in Excel using Control Panel

If you want to do the date formatting in Excel using the Control Panel, you can also do it by:

Step 1: Enter Control Panel > click select Region and Language > Change the date, time or number format as desired in the customization window.

Step 2: On the Format tab, set the date format by clicking the arrow next to the format you want and selecting from the drop-down list to finish.

Step 3: Press OK to apply the new year date format.

Step 4: Go back to Excel and add the date to the Excel file to see the results change.

Format date in Excel using . function

The functions used in the date format in Excel are DATEVALUE() and TIMEVALUE().

• Use the DATEVALUE() function to convert Excel dates to numbers =DATEVALUE(“1/1/2015”).
• Use the TIMEVALUE() function to get the decimal number representing the time =TIMEVALUE(“6:00 AM”).

To get both the date and time, you can use the function like this:

=DATEVALUE(“1/1/2015”) & TIMEVALUE(“6:00 AM”)

Date format error in Excel

In the date format in Excel, sometimes we will encounter the following errors.

One cell is not large enough to hold an entire day

If you see a cell with a pound sign (#####) instead of a date, it’s likely that your cells aren’t wide enough to accommodate the entire date.

Solution: Drag the border in that column to the right to expand the column size, from which the cell is also expanded.

Negative numbers are formatted as dates

Microsoft Excel 2013, 2010 and 2007 versions, the pound sign (#####) is also displayed when there is a negative value in the date and time format cells.

Solution: Use the TEXT function.

Assuming you are taking C1-B1 and output a negative value, then use the following formula to output the result in date format.

=TEXT(ABS(B1-C1),”-d-mmm-yyyy”)

Dates are entered into Excel as text values

When you import data into Excel from a .csv file, dates are usually entered as text values ​​rather than numbers.

Solution: Convert Excel date format to number using Excel’s DATEVALUE function.

See more:

Above are 3 very simple ways to format dates in Excel that GhienCongList introduces to you. Don’t forget to share the article with your friends.