Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

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
0
sullisnyc44
Asked:
sullisnyc44
  • 2
1 Solution
 
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
 
irinucCommented:
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now