[Webinar] Streamline your web hosting managementRegister Today

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

Start Date calculated solumn = Wrong

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
sullisnyc44
Asked:
sullisnyc44
  • 8
  • 3
  • 3
1 Solution
 
chapmanjwCommented:
You could try =DATE(YEAR([Start Time]),MONTH([Start Time] + 1),DAY([Start Time]))
0
 
chapmanjwCommented:
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
 
sullisnyc44Author Commented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
chapmanjwCommented:
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
 
sullisnyc44Author Commented:
ah. ok I will just confirm that the behavior is consistent. Thanks for the response.

0
 
sullisnyc44Author Commented:
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
 
irinucCommented:
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
 
sullisnyc44Author Commented:
Thanks for the response.

So my formula would be:

=date(text([StartTime])

?
0
 
irinucCommented:
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
 
sullisnyc44Author Commented:
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
 
sullisnyc44Author Commented:
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
 
sullisnyc44Author Commented:
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
 
sullisnyc44Author Commented:
This is what worked - thanks for the answer!

=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Start Time]+1,[Start Time])
0
 
irinucCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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