Solved

Start Date calculated solumn = Wrong

Posted on 2010-09-07
14
866 Views
Last Modified: 2012-05-10
I have a standard column from the Microsoft Fab 40 absence tracker that has the Start Date & time. This can't be edited so I wanted to create a 'display column' to show just the date in the views.

HOWEVER the date is off by one day! So if the [Start Time] = 2/22/2010 12:00 AM the [Start Date] displays as '2/21/2010'

[Start Date] is a calculated column where the formula is '=[start time]'




I think Sharepoint will be the death of me...
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
  • 8
  • 3
  • 3
14 Comments
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33619397
You could try =DATE(YEAR([Start Time]),MONTH([Start Time] + 1),DAY([Start Time]))
0
 
LVL 21

Accepted Solution

by:
chapmanjw earned 500 total points
ID: 33619404
Actually:

DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time] + 1))

I put the +1 on the Month in the last post by accident ;)
0
 

Author Comment

by:sullisnyc44
ID: 33620595
thanks for the formula... but why do I have to do that at all? I'd like to understand the logic about why this is happening. To me it seems like a no-brainer - show me the Date Only. Or am I missing something?

I'm not 100% sure that this behavior is actually consistent for each item - I must examine the data thoroughly.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 21

Expert Comment

by:chapmanjw
ID: 33621004
I tried it on my machine and it is doing the same thing.  Not entirely sure why, but that is how I worked around it.
0
 

Author Comment

by:sullisnyc44
ID: 33621735
ah. ok I will just confirm that the behavior is consistent. Thanks for the response.

0
 

Author Comment

by:sullisnyc44
ID: 33657049
The behavior is not consistent. The last column that is just a number" = Day[Start Time]"

Here are my results
Attendance item      System Account      Vacation      9/8/2009 12:00 AM      9/8/2009      7
Attendance item      System Account      Vacation      9/8/2009 12:00 AM      9/8/2009      7
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      Vacation      10/16/2009 12:00 AM      10/17/2009      16
Attendance item      System Account      Vacation      12/11/2009 12:00 AM      12/12/2009      11
Attendance item      System Account      Vacation      12/28/2009 12:00 AM      12/29/2009      28
Attendance item      System Account      Other      1/20/2010 12:00 AM      1/21/2010      20
Attendance item      System Account      Other      1/28/2010 12:00 AM      1/29/2010      28
Attendance item      System Account      Other      2/5/2010 12:00 AM      2/6/2010      5
Attendance item      System Account      Other      2/8/2010 12:00 AM      2/9/2010      8
Attendance item      System Account      Other      2/16/2010 12:00 AM      2/17/2010      16
Attendance item      System Account      Bulova      2/22/2010 12:00 AM      2/23/2010      22
Attendance item      System Account      Funeral      2/23/2010 12:00 AM      2/24/2010      23
Attendance item      System Account      Other      3/4/2010 12:00 AM      3/5/2010      4
Attendance item      System Account      Other      3/12/2010 12:00 AM      3/13/2010      12
Attendance item      System Account      Other      3/15/2010 12:00 AM      3/16/2010      15
Attendance item      System Account      Other      3/26/2010 12:00 AM      3/27/2010      26
Attendance item      System Account      Other      3/31/2010 12:00 AM      3/1/2010      31
Attendance item      System Account      Other      4/8/2010 12:00 AM      4/9/2010      8
Attendance item      System Account      Other      4/16/2010 12:00 AM      4/17/2010      16
Attendance item      System Account      Other      4/23/2010 12:00 AM      4/24/2010      23
Attendance item      System Account      Other      4/30/2010 12:00 AM      4/1/2010      30
Attendance item      System Account      Other      5/7/2010 12:00 AM      5/8/2010      7
Attendance item      System Account      Other      5/14/2010 12:00 AM      5/15/2010      14
Attendance item      System Account      Vacation      5/17/2010 12:00 AM      5/18/2010      17
Attendance item      System Account      Other      5/21/2010 12:00 AM      5/22/2010      21
Attendance item      System Account      Other      6/9/2010 12:00 AM      6/10/2010      9
Attendance item      System Account      Other      6/18/2010 12:00 AM      6/19/2010      18
Attendance item      System Account      Other      6/25/2010 12:00 AM      6/26/2010      25
Attendance item      System Account      Other      6/28/2010 12:00 AM      6/29/2010      28
Attendance item      System Account      Funeral      8/6/2010 12:00 AM      8/7/2010      6
Attendance item      System Account      Vacation      9/1/2010 12:00 AM      9/2/2010      1
0
 
LVL 9

Expert Comment

by:irinuc
ID: 33660041
I think this is related to the "all day event" thing.
If the event is an all day event then the formula that you have will be correct; if the event is not an all day event, your formula will be incorrect (it doesn't need the +1)
A collegue of mine had a similar situation and he ended up by transforming the date to TEXT and then back to date.

hope this helps
0
 

Author Comment

by:sullisnyc44
ID: 33682574
Thanks for the response.

So my formula would be:

=date(text([StartTime])

?
0
 
LVL 9

Expert Comment

by:irinuc
ID: 33682908
Actually, it was not that easy as to transform directly: basically you would need to add 1 to the formula that you already have when the event is "all day event". To check if it is a all day event, then you need to check if the difference between the 2 dates is 23:59.
Here is the formula i would use to get StartTime:
=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Start Time]+1,[Start Time])

i used the examples here:
http://office.microsoft.com/en-gb/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx
0
 

Author Comment

by:sullisnyc44
ID: 33685006
I automatically set the events to all day events with jquery. however it still records the time as 12:00am and the end time as 11:59.

[Start Time] is the standard field name for calendars. It records the Date & Time.

So I can't change that input field.

My goal is to eliminate the time from showing in a calculated column. The issue is that it's displaying the wrong date no matter what I do.

0
 

Author Comment

by:sullisnyc44
ID: 33685243
Here are some of my results when I attempt to display the Date as  text.

StartDateTESTDay=DAY([Start Time])
StartDate=TEXT([Start Time],"mm/dd/yyyy")


Type               Start Time                          Start Date    StartDateTESTDay
Vacation      9/8/2009 12:00 AM      09/07/2009      7
Vacation      9/8/2009 12:00 AM      09/07/2009      7
Vacation      9/8/2009 12:00 AM      09/08/2009      8

All of the entries show up on the correct Day in the calendar - 9/8/2009
0
 

Author Comment

by:sullisnyc44
ID: 33692105
oh my - thanks so much! this did solve it - I can't believe it. If I could award you more than 500 points I would. Thank you thank you thank you thank you

=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Start Time]+1,[Start Time])
0
 

Author Comment

by:sullisnyc44
ID: 33692183
This is what worked - thanks for the answer!

=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Start Time]+1,[Start Time])
0
 
LVL 9

Expert Comment

by:irinuc
ID: 33692224
Glad to hear it worked; I will send the "thank you" to my colleague who told me the "23:59" trick :D
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc. The most natu…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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