Solved

Start Date Calculated Column is STILL = WRONG :(

Posted on 2010-09-12
4
605 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Failed to upgrade SharePoint Products 8 72
Is there a SharePoint setup guide for beginners? 18 59
Redesign the sharepoint site. 9 64
Sharepoint 2010 Auditing for One Site 3 50
Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

786 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