Carlton Collins ( is a technology consultant, a CPE instructor, and a JofA contributing editor. This approach will ensure that any newly added worksheets don't contain General number formatting. Important caveat: Because additional worksheets created in those new workbooks (created using the New Sheet command) will contain General formatting by default, you should consider adding any new worksheets by duplicating an existing worksheet that already bears the non- general format (using the Ctrl+Drag Tab method described in the April 2011 Technology Q&A topic ' Creating a New Worksheet Can Be a Drag'). Note This is valid for older versions of Excel. Solution: Right-click the cell containing the Date, select ‘Format Cells’, click ‘Date’ present under Number à Category and finally choose a Date format of choice (Example: DD/MM/YYYY format). Thereafter, each new Excel workbook you create will no longer automatically apply the auto- formatting functionality. The cell is set to the Number format, which converts the input to a numerical value. This is done by saving the workbook as an Excel Template named Book.xltx in the XLStart folder. You could then take matters one step further by saving a blank copy of this altered workbook (with this global format change) as your default workbook. Thereafter, the auto- formatting functionality is effectively disabled. Next, apply a different number format (such as the Accounting format with zero decimal places and no dollar sign symbols) from the Home tab's Number group. To do this, select the entire workbook as follows: Select all sheet tabs by right- clicking a worksheet tab and clicking the Select All Sheets option, and then clicking the leftmost top corner of one of the worksheets. While there isn't a specific option for disabling the General format's auto- formatting functionality, you can effectively disable it with a simple trick: Change your workbook's default number format to something other than the General number format. Usually, CPAs tend to appreciate the General format because it can save time, but sometimes this adoptive format behavior can be counterproductive-for example, when such format changes aren't desired. Once a cell adopts the new number format, the General number format no longer applies, and the cell will keep the newly applied number format even if numbers with differing formats are subsequently entered. As examples, if you type $45.55 into a cell with General formatting, the cell automatically becomes formatted as currency with two decimal places, or if you enter 37.1%, the cell becomes formatted as a percentage with one decimal place. This is because, as a default, Excel worksheets are globally formatted using the General format, which automatically adopts the number format you use to initially enter numbers into a cell. Copy the formula to all other necessary cells.However, these adjustment options do not completely disable Excel's automatic application of number formats as data are entered. In the 'Format Cells' dialog box, on the 'Number' tab, select the category 'Custom,' and choose the type 'h:mm' or 'h:mm:ss' depending on whether seconds need to appear.ħ. Right click on the cell where the formula was entered and select 'Format Cells.'ĥ. Copy the formula to all other necessary cells. NOTE: Even if seconds are not originally appearing in the time, they must be included in the formula for it to work.Ĥ. To do this, select the entire workbook as follows: Select all sheet tabs by right - clicking a worksheet tab and clicking the Select All Sheets option, and then clicking the leftmost top corner of one of the worksheets. Example: In cell B2 type the following if the date and time were in cell A2: =time(hour(a2),minute(a2),second(a2)) Type the following: =time(hour(cellname),minute(cellname),second(cellname))Ī. Go to the cell where you wish have the time without the date (cannot be the current cell with the date). Removing Date from Data with Date and Timeġ. Example: In cell B2 type the following if the date and time were in cell A2: =date(year(a2),month(a2),day(a2))Ĥ. Type the following: =date(year(cellname),month(cellname),day(cellname))Ī. Go to the cell where you wish have the date without the time (cannot be the current cell with the date). Removing Time from Data with Date and Timeġ.

There are often instances in which you have date and time information in a single column in an Excel spreadsheet and it is necessary to remove one or the other. Removing the time or date from cells in Excel can be done easily with a few simple steps.

Remove custom formatting excel