%20(11).png){kind=small}
Recording and maintaining data is the prime function any person can perform with some basic knowledge to excel. While storing data is more accessible, cleaning data might require making new rows or columns, understanding numbers, etc. Data cleaning for numbers often requires the formatting of dates; the reasons could be that the format you use is different from the format in the downloaded file, or maybe while downloading, the dates got converted into numbers, and so on.
Here are five different ways to adjust date formats in Excel.
Date as number
Whenever we enter a date in Excel, we type it as 10/2/2020 or 10 February 2020, and so on; each date in Excel on a backend is provided a numerical value. To check the numerical value for the date, ten Feb 2020, enter the date in any cell, go to the Home tab> Number window, and change the date option to number. This is done to make some calculations and perform date-related functions like the day's function, network days function, etc.
Date as text
A date can be mentioned in many formats, such as dd/mm/yy or mm/dd/yyyy and many others; irrespective of the format, every date is given a numerical value. Suppose you use dates only to maintain records. It is recommended to convert the dates as text. In that case, this eliminates the risk of dates being converted into numbers on transferring the data. The function is =TEXT(cell_ref," dd, mmmm,yyyy") to restore the data into text format. This way, the date 10-02-2020 will be converted into the format 10 February 2020.
Date as Day
We again use the text function to get the day on a particular date. Suppose the date is 10 December 2029, and you want to find out what day it will be; in that case, you can use the text function as =TEXT(cell_ref," dddd"). The result will be on Monday after applying the formula in Excel.
Date as Long date
An extended date is the one which also shows the day, month, year, and weekday. Many long dates are assisted with time too. To convert a regular date, say, 10-02-2020, into a long date, Select the cell with the date> Number Window> Select the option more number formats and choose the format as per your requirement.
Converting dates
While downloading data reports from different sources, we often notice a date mentioned as a text, say 10 February 2020, as 10.2.2020. The dots used to separate date from month and year make it challenging to understand in such a scenario. It is recommended to use the Find and Replace function. Where you find the dots and replace them with a hyphen or a slash, this automatically converts the format of the cells to dates.
Adjusting dates is of prime importance when cleaning data. However, it can be fun, too. Try using the date formats you like, and let us know in the comments section which format you like the most.
Comments