Solved

Start Date Calculated Column is STILL = WRONG :(

Posted on 2010-09-12
4
603 Views
Last Modified: 2012-05-10
I am simply trying to display a date without the time. I'm getting inconsistent results. Someone suggested that I simply add a day but that is not consistent.

I created a calcualted column to show me the day that Sharepoint thinks the time value has. The results are disappointing to say the least

StartDate is a calculated column = =DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]+1))
*sometimes this formula displays the right info, sometimes not

StartDateTESTDay = Day([Start Time])

Title / Created By / Absence Type/ Start Time / StartDate / Day (StartDateTESTDay)
Attendance item      System Account      Other      1/20/2010 12:00 AM      1/21/2010      20
Attendance item      System Account      Other      1/20/2010 12:00 AM      1/21/2010      20
Attendance item      System Account      Other      5/21/2010 12:00 AM      5/22/2010      21
Attendance item      System Account      Other      5/21/2010 12:00 AM      5/22/2010      21
Attendance item      System Account      Business      2/22/2010 12:00 AM      2/23/2010      22
Attendance item      System Account      Business      2/22/2010 12:00 AM      2/23/2010      22
Attendance item      System Account      Personal      2/23/2010 12:00 AM      2/24/2010      23
Attendance item      System Account      Personal      2/23/2010 12:00 AM      2/24/2010      23
Attendance item      System Account      Other      4/23/2010 12:00 AM      4/24/2010      23
Attendance item      System Account      Other      4/23/2010 12:00 AM      4/24/2010      23
Attendance item      System Account      Other      6/25/2010 12:00 AM      6/26/2010      25
Attendance item      System Account      Other      6/25/2010 12:00 AM      6/26/2010      25
Attendance item      System Account      Other      3/26/2010 12:00 AM      3/27/2010      26
Attendance item      System Account      Other      3/26/2010 12:00 AM      3/27/2010      26
Attendance item      System Account      Vacation      12/28/2009 12:00 AM      12/29/2009      28
Attendance item      System Account      Vacation      12/28/2009 12:00 AM      12/29/2009      28
Attendance item      System Account      Other      1/28/2010 12:00 AM      1/29/2010      28
Attendance item      System Account      Other      1/28/2010 12:00 AM      1/29/2010      28
Attendance item      System Account      Other      6/28/2010 12:00 AM      6/29/2010      28
Attendance item      System Account      Other      6/28/2010 12:00 AM      6/29/2010      28
Attendance item      System Account      Jury Duty      9/29/2009 12:00 AM      9/30/2009      29
Attendance item      System Account      Jury Duty      9/29/2009 12:00 AM      9/30/2009      29
Attendance item      System Account      Jury Duty      9/30/2009 12:00 AM      9/30/2009      29
Attendance item      System Account      Jury Duty      9/30/2009 12:00 AM      9/1/2009      30
Attendance item      System Account      Other      4/30/2010 12:00 AM      4/1/2010      30
Attendance item      System Account      Other      4/30/2010 12:00 AM      4/1/2010      30
Attendance item      System Account      Other      3/31/2010 12:00 AM      3/1/2010      31
Attendance item      System Account      Other      3/31/2010 12:00 AM      3/1/2010      31
0
Comment
Question by:sullisnyc44
  • 2
4 Comments
 
LVL 2

Expert Comment

by:Hossy
ID: 33657127
Try examining YEAR, MONTH, DAY, HOUR, MINUTE, SECOND for StartDate first.  You might find that the underlying data is different from StartDate by itself.  This could be due to rounded or timezone differences, but you should be able to identify it by breaking it down.

Another thing to try is this:
=CONVERT(DATE,StartTime,101)
or
=CONVERT(DATETIME,StartTime,101)

Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Comment

by:sullisnyc44
ID: 33657323
convert is a SQL funtion? because the sharepoint column does not like it.
0
 
LVL 9

Accepted Solution

by:
irinuc earned 500 total points
ID: 33683012
See if this helps
=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Start Time]+1,[Start Time])

http://office.microsoft.com/en-gb/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx
0
 

Author Closing Comment

by:sullisnyc44
ID: 33692121
It took a really long time and multiple postings to get this answer. I'm so relieved to finally have it.


thank you thank you thank you thank you
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have just recently built a new SharePoint 2007 farm on a complete Windows 2008 R2 server platform and part of my standard build procedure is to implement a warm up routine, usually in the form of a script that is scheduled every morning to launch …
When installing SharePoint 2010 RTM I came across a strange error, I was getting timeouts during the installation. I searched the web and found the best solution to be found here (http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010genera…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now