background preloader


Facebook Twitter

Convert date/time value to a date value (remove time portion) in Excel 2003/XP/2000/97. Question: In Microsoft Excel 2003/XP/2000/97, how do I convert a column with a date format to date only, no time?

Convert date/time value to a date value (remove time portion) in Excel 2003/XP/2000/97

Answer: You can remove the time portion from a date by using the Year function, Month function, and Day function. We'll demonstrate with the example below: In this example, we have a date/time value in cell A1 and we'd like to remove the time portion from this value. In cell B1, we've entered the following formula: The order of how you use the Year, Month, and Day functions will depend on how you're formatting your date. In this example, we've formatted cell B2 with the following format: You may need to reorder the Year, Month, and Day functions based on your Date format selected in Excel.

Question: I tried your solution above: But Excel did not recognize the field as a date field. Excel Convert Text to Date ~ Yogesh Gupta's Excel Tips. Many of us get into a situation when the dates in our data are coded as text.

Excel Convert Text to Date ~ Yogesh Gupta's Excel Tips

Excel does not recognise such text entries as date and we can not use them for any calculation purpose. However you can easily covert them to date by the help of excel formula. You may have a situation where the text of the date is actually as date but being a text string it is not recognised as date by excel. It could be 12/Oct/2009 in text string. You can easily convert them using Datevalue formula. However many time we get data stored like 20091012. Dates And Times In Excel. How Excel Stores Dates And Times Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt .

Dates And Times In Excel

This is called a serial date, or serial date-time. Dates The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have passed since 1900-Jan-0. Actually, this number is one greater than the actual number of days. Times The fractional portion of the number, ttttt, represents the fractional portion of a 24 hour day.

As you can see, any date and time can be stored as the sum of the date and the time. Date Arithmetic. Adding Dates You can add some number of days to a date by simply using the =SUM function. Since Excel stores dates as a number of days, no further work is required. For example, to add 5 days to 1/1/98, in A1, use =A1+5, which gives 1/6/98. To add a number of months or years to a date, you first need to decompose the initial date into its year, month, and day components, add in the desired offset, and then have Excel put the components back together. For example, say you have a date in A1, to which you want to add 3 months and 4 days.

Excel will automatically handle the situation which arises when you pass a number greater than 12 to the MONTH function, or a number greater than 31 to the DAY function. For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula gives 3/7/98. Generally, you cannot add two dates that are in serial format.

Convert dates stored as text to dates. Occasionally, dates may become formatted and stored in cells as text.

Convert dates stored as text to dates

For example, you may have entered a date in a cell that was formatted as text, or the data might have been imported or pasted from an external data source as text. Dates that are formatted as text are left-aligned instead of right-aligned in a cell. With Error Checking turned on, text dates with two-digit years might also be marked with an error indicator Because Error Checking in Microsoft Office Excel can identify text-formatted dates with two-digit years, you can use the automatic correction options to convert them to date-formatted dates. You can use the DATEVALUE function to convert most other types of text dates to dates. What do you want to do? Convert text dates with two-digit years by using Error Checking Click the Microsoft Office Button , and then click Excel Options. Note The selected cells must be adjacent.