Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Start Date calculated solumn = Wrong

Posted on 2010-09-07
Medium Priority
869 Views
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
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
• 8
• 3
• 3

LVL 21

Expert Comment

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

LVL 21

Accepted Solution

chapmanjw earned 2000 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

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

LVL 21

Expert Comment

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

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

0

Author Comment

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

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

ID: 33682574
Thanks for the response.

So my formula would be:

=date(text([StartTime])

?
0

LVL 9

Expert Comment

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

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

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

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

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

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

Question has a verified solution.

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

I recently came across an issue with a MOSS 2007 deployment where access into some sub-sites were denied, even for the MOSS farm administrators. A bit of background to the setup of this MOSS farm; this was a three server setup, consisting of a frâ€¦
A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one toâ€¦
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (httpsâ€¦
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized â€¦
###### Suggested Courses
Course of the Month7 days, 9 hours left to enroll