Or, in Excel you could create a new column and use the DATE function to create a true date based on the date stored as text. There are two solutions to this problem: You could change the date system that your computer uses to match the date system you want to type in Excel. However, if you typed a date like dd/mm/yy, then Excel wouldn't recognize that as a date. If you typed a date like that in a cell, Excel would recognize it as a date and you'd be able to use it in a subtraction formula. If a cell's date isn't entered using the same date system, then Excel won't recognize it as a true date.įor example, let's say that your computer displays dates as mm/dd/yyyy. Repeat this process for other columns to ensure they don't contain leading spaces before dates.Įxcel uses your computer's date system. On Step 3 of 3 of the wizard, under Column data format, click Date.Ĭhoose a date format, and then click Finish. Select the column that contains the date by clicking its column header. If you don't see one or more spaces, go to the next section on checking your computer's date settings. If your cell has this problem, proceed to the next step.

Here's what a selected space looks like at the beginning of a cell: Put your cursor at the beginning and see if you can select one or more spaces. Here are some solutions that can help this problem.ĭouble-click a date that is being used in a subtraction formula.

And Excel won't recognize text as a date. But if it's aligned to the left, this means the date isn't really a date. If your date is aligned to the right, then it's a date. Repeat this process for other cells that have the error. If there are commas where there should be minus signs for subtraction, change them to minus signs. If a cell contains a #VALUE! error, double-click to edit it. However, another list separator might be more appropriate for your particular region. For example, a comma is a common list separator. If the List separator is set to the minus sign, change it to something else. On the Formats tab, click Additional settings. Windows 7: Click Start and then type Region, and then click Region and language. Windows 8: At the Start screen, type Region, click Settings, and then click Region. Windows 10: Click Start, type Region, and then click the Region control panel. In Windows, open your Region control panel. If you don't get the error, try other solutions on this page. If you get the #VALUE! error, go to the next step. The following might solve your problem:įirst do a quick test. If you're using Windows, you might get the #VALUE! error when doing even the most basic subtraction formula.

Formula to subtract cells in excel