sullisnyc44
asked on
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
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
ASKER
convert is a SQL funtion? because the sharepoint column does not like it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
thank you thank you thank you thank you
Another thing to try is this:
=CONVERT(DATE,StartTime,10
or
=CONVERT(DATETIME,StartTim
Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx