MOSS Forum

Ask Question   UnAnswered
Home » Forum » MOSS       RSS Feeds

Quirk in calculated date fields?

  Asked By: Eve    Date: Jul 15    Category: MOSS    Views: 1842

I came upon this weirdness in calculated date fields and was wondering if anyone
else has run into it.

I create a calculated field for a form or document library on a site
(MOSS/WSSV3). The calculated field uses the formula: "=[Modified]+7" so it can
be used in date comparisons from a dataview.

The field is ok for a while, showing the date 7 days after each item was
modified as expected. Then for some reason at a later point, the calculated
date returned in that field becomes 1/6/1900 for every list item, regardless of
the actual modification date. The modified field itself seems ok, but all of
the calculations based on it go awry for some reason. The field is defined as
date and time type, with date and time format.

I'm sure someone would have discovered this by now if it were a bug, so I'm
thinking it must be some problem with the forumula, but what??

I noticed that when I re-open the field from List Settings, it shows the formula
without brackets, ("[Modified]" has become "Modified"). If I manually add the
brackets back in, the forumula begins working again for a while. Then after a
while of normal site use, the same thing happens again, the dates revert to
1900. I haven't yet figured out exactly what causes the reversion.

Any idea what is going on here?



8 Answers Found

Answer #1    Answered By: Zoe Cotton     Answered On: Jul 15

I have not seen this but it is commonplace for the square brackets to be
removed from formulas when they are saved to the database.

Answer #2    Answered By: Alexander Scott     Answered On: Jul 15

I've had issues with trying to group by date. I have a field  that that I
group by and invariably if the date  has for example 1/8/2008 it will show up
under the group 1/7/2008, it's subtracting a day every time.

Haven't bothered trying to figure it out yet, I just know it's messed up and
make allowances.

Answer #3    Answered By: Benito Carey     Answered On: Jul 15

We are getting the same error when trying to group.

Answer #4    Answered By: Ebony Perkins     Answered On: Jul 15

I am not sure about the calculated  date issue but I have also
encountered the date  being off by one. I ended up create  a custom web
control that derives from the field  Value control and handles the
issue if it is a Date value.

Answer #5    Answered By: Anu K     Answered On: Jul 15

That being the case, I guess the brackets are not required syntax? Otherwise
calculated fields  would always break as soon as the formula  was saved to the
database, wouldn't they?

So I'm still wondering why my calculated  date fields don't work for new items in
the list, and why they stop working  for an item  whenever I modify the properties
of that item. And for some reason, adding brackets back  in to the formula
happens to fix the problem  temporarily. Very puzzling.

I can't ask administrators to keep opening up every list  and adding brackets
back into formulas every day ... at least not with a straight face.

Answer #6    Answered By: Josie Barron     Answered On: Jul 15

If what I'm seeing is typical, then it appears that my conclusion should be that
calculated date  fields using the [Modified] keyword are actually too unstable to
use for more than a few minutes!

That is, every time  someone modifies an item, the calculated  date fields  with
the formula  "={Modified]+7" (or anything similar) get clobbered.

That means someone has to manually  refresh the list  settings for those columns
to re-enter the formula every time before they run  a report of files modified  in
the past 7 days. I hope I'm wrong, but I'm starting to get that sickening
feeling from reading the newsgroups that this clobbering of the calculated date
fields is an "expected behavior" and people end up writing elaborate custom code
to get around it.

Answer #7    Answered By: Tamika Cummings     Answered On: Jul 15

Sounds like this may be a bug  that should be reported to Microsoft.

Answer #8    Answered By: Linsey Bauer     Answered On: Jul 15

yeah, I did that quite a while ago. Never got any kind of response.

Didn't find what you were looking for? Find more on Quirk in calculated date fields? Or get search suggestion and latest updates.