Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Start Date Calculated Column is STILL = WRONG :(

Posted on 2010-09-12
4
Medium Priority
?
613 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Screencast - Getting to Know the Pipeline

610 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