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.

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.

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.

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.

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.

=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.