#VALUE!

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

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

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


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
 
Back
Top