#VALUE!
We have a spreadsheet with the following formula in a column of cells:
=B7+54-$E$3+K7 Column B contains a date, E3 contains the current date and K7 contains a number of days. When a date is entered in column B, it calculates a new value and deposits that value (a number of days) into the cell with the formula. This works for every user but one. When he enters a date in a cell in column B, the cell with the formula displays #VALUE! instead of a number. I have not been able to figure out what is different about this user and any help would be appreciated. Thanks. |
#VALUE!
Maybe the user is adding spaces before the date to make the column align and
look pretty!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Glenn" wrote in message ... We have a spreadsheet with the following formula in a column of cells: =B7+54-$E$3+K7 Column B contains a date, E3 contains the current date and K7 contains a number of days. When a date is entered in column B, it calculates a new value and deposits that value (a number of days) into the cell with the formula. This works for every user but one. When he enters a date in a cell in column B, the cell with the formula displays #VALUE! instead of a number. I have not been able to figure out what is different about this user and any help would be appreciated. Thanks. |
#VALUE!
Perhaps his column B is formatted as text, so although the date may
look like a normal date it is in fact a text value and you cannot perform any arithmetic on it. Just ensure that the cells in column B are formatted as General (or as Date) before he enters any more dates. Hope this helps. Pete On Dec 24, 4:45*pm, Glenn wrote: We have a spreadsheet with the following formula in a column of cells: =B7+54-$E$3+K7 Column B contains a date, E3 contains the current date and K7 contains a number of days. When a date is entered in column B, it calculates a new value and deposits that value (a number of days) into the cell with the formula. This works for every user but one. When he enters a date in a cell in column B, the cell with the formula displays #VALUE! instead of a number. I have not been able to figure out what is different about this user and any help would be appreciated. Thanks. |
#VALUE!
Or he may have different Windows regional settings, so if he tries to enter
a date as 24/12/2007 or 12/24/2007, depending on the settings, it may not be recognised as a date. [And if he enters 2/1/2008 or 1/2/2008 it may be interpreted as the wrong date.] -- David Biddulph "Pete_UK" wrote in message ... Perhaps his column B is formatted as text, so although the date may look like a normal date it is in fact a text value and you cannot perform any arithmetic on it. Just ensure that the cells in column B are formatted as General (or as Date) before he enters any more dates. Hope this helps. Pete On Dec 24, 4:45 pm, Glenn wrote: We have a spreadsheet with the following formula in a column of cells: =B7+54-$E$3+K7 Column B contains a date, E3 contains the current date and K7 contains a number of days. When a date is entered in column B, it calculates a new value and deposits that value (a number of days) into the cell with the formula. This works for every user but one. When he enters a date in a cell in column B, the cell with the formula displays #VALUE! instead of a number. I have not been able to figure out what is different about this user and any help would be appreciated. Thanks. |
#VALUE!
On my XL97 machine, pre-formatting B7 to text, *and* preceding the date
entry with an apostrophe, *still* returned a proper calculation. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Pete_UK" wrote in message ... Perhaps his column B is formatted as text, so although the date may look like a normal date it is in fact a text value and you cannot perform any arithmetic on it. Just ensure that the cells in column B are formatted as General (or as Date) before he enters any more dates. Hope this helps. Pete On Dec 24, 4:45 pm, Glenn wrote: We have a spreadsheet with the following formula in a column of cells: =B7+54-$E$3+K7 Column B contains a date, E3 contains the current date and K7 contains a number of days. When a date is entered in column B, it calculates a new value and deposits that value (a number of days) into the cell with the formula. This works for every user but one. When he enters a date in a cell in column B, the cell with the formula displays #VALUE! instead of a number. I have not been able to figure out what is different about this user and any help would be appreciated. Thanks. |
#VALUE!
=B7+54-$E$3+K7
Even if all 3 referenced cells were preformatted as TEXT the calculation would still return the correct result (although it would also be a TEXT number). So, there's something else going on. Leading/trailing spaces. See David's reply about regional date settings. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Perhaps his column B is formatted as text, so although the date may look like a normal date it is in fact a text value and you cannot perform any arithmetic on it. Just ensure that the cells in column B are formatted as General (or as Date) before he enters any more dates. Hope this helps. Pete On Dec 24, 4:45 pm, Glenn wrote: We have a spreadsheet with the following formula in a column of cells: =B7+54-$E$3+K7 Column B contains a date, E3 contains the current date and K7 contains a number of days. When a date is entered in column B, it calculates a new value and deposits that value (a number of days) into the cell with the formula. This works for every user but one. When he enters a date in a cell in column B, the cell with the formula displays #VALUE! instead of a number. I have not been able to figure out what is different about this user and any help would be appreciated. Thanks. |
All times are GMT +1. The time now is 03:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com