Start Date Calculated Column is STILL = WRONG :(

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
sullisnyc44Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
irinucConnect With a Mentor Commented:
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
 
HossyCommented:
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
 
sullisnyc44Author Commented:
convert is a SQL funtion? because the sharepoint column does not like it.
0
 
sullisnyc44Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.