Solved

Start Date calculated solumn = Wrong

Posted on 2010-09-07
14
865 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

740 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