How do I insert date in dd mm yyyy format in Excel?


How do I insert date in dd mm yyyy format in Excel? share your thoughts

in progress 0
12 months 1 Answer 168 views 0

Answer ( 1 )

  1. You can enter dates in excel using different formats. But the problem arises when you want to convert them back to the original format. For example, if you entered 01/01/2016 in cell A1 and wanted to convert it back to 01 Jan 2016, you cannot simply copy paste because the format changes.

    So, how do you solve this issue? There are several ways to achieve this goal. Let us look at them one by one.

    Method 1: Using Text To Date Function

    This method involves entering the date in text format and converting it back to date format.

    For Example: Entering 01/01/2016 as TEXT in Cell A1 and copying it down to fill the entire column. Now select the whole column and press Ctrl + Shift + F9. In the dialog box that appears, click on Convert Text to Date. After clicking OK, the date will appear in the desired format.

    Method 2: Using Formula

    Another method is to use formula. In this case, you need to create a function which converts the data type of cell to text and vice versa.

    Let us assume that you have created a function named fnc_ConvertDate() in a module.

    Now, you can use this function whenever required.



    Date formatting in Excel

    Excel dates are formatted differently than most other types of dates. Dates are represented as numbers, not words, so they must be converted to a different type of number when inserted into a spreadsheet.

    To convert a date to a numeric value, use this formula:DATEVALUE(TEXT(“dd/mm/yyyy”))

    This formula converts the date string “01/02/2016” to 1/2/2016.

    Formatting dates in Excel

    Excel is a great tool for tracking sales, expenses, and income. But when you’re trying to analyze these numbers over time, formatting dates correctly becomes important.

    If you use Excel to track your finances, you need to be able to quickly identify months, quarters, years, etc., and this requires knowing how to properly format dates.

    To format dates in Excel, simply enter the date in the cell, right-click the cell, and select Format Cells…

    Then select Number tab and change the Date Style to Short Date. This formats the date according to the standard dd/mm/yyyy format.

    This method works well for most situations, but there may be times when you need to enter a specific date format, such as dddd MMMM YYYY. To do this, go back to the Cell Format menu and select Custom. Then, type the desired date format (e.g., dddd MMM YYYY) and press Enter.

    Now, whenever you enter a date in the cell, Excel automatically formats it according to the custom date format you specified.

    Inserting date into cell

    If you’re working with dates in Excel, there are two ways to enter them into cells. The first method is to use the built-in Date function. This works well when you need to add a single date value to a spreadsheet. However, if you need to add multiple dates to a row, this method becomes cumbersome because you must manually type each date.

    To avoid typing out long strings of numbers, you can instead use the built-in Text to Columns feature. To do this, select the entire column containing dates, right-click, and select Convert to Text. Then, select Delimited (Comma) and press OK. Finally, drag the converted column to another location in your spreadsheet.

    Now, whenever you need to add a date to a cell, simply copy the contents of the converted column and paste them into the desired cell.


    It’s important to know how to properly format dates in Excel so they look professional when you’re presenting them to clients. In this video, we’ll show you how to do just that!