SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a calculated field to work out the length of an event in days, as follows:

=DATEDIF([Start Time],[End Time],"d")

I live in the UK and we're currently on BST; so if I create an event in GMT after the clocks change it calculates the day as one less than it actually is.

More intresting if I set the time as UTC: Universal coordinated time it is wrong for the whole year not just when the clocks are changed, IF I select UTC+1 or UTC-1 or other time zones howerver it calcualted correctly. It seems strange that if this is a bug it has not been picked up yet. Can anyone replicate?

share|improve this question

There is a ROUNDDOWN function. You might want to take a look at this list of calculated references available, just used it a few minutes ago to change up some display logic using IF and ISERROR.

share|improve this answer
I did try this and could not get what I wanted. I think the problem is the dateif allready rounds down to the highest whole day value. I have now edit my question however as allwasy calculated wrong under UTC. I am starting to think this is bug in SP. – James Heard May 25 '11 at 14:07

I have not worked out why UTC time does not work but as a work around am using

ROUNDUP([End Time]-[Start Time],0)

And it seems to be working. I need to test it works across all time zones first and not just UTC.

I am going to contact MS as i think the UTC thing must be a bug. Would like to know if someone can re-produce. I am using Search sever express version of SP.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.