Solved

Start Date calculated solumn = Wrong

Posted on 2010-09-07
14
862 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Summary In SharePoint 2010 it is easy to create custom color themes to jazz up a site. Theme colors can also be created in PowerPoint 2010 with a few clicks. But how do the chosen colors actually look in the SharePoint site? The attached PowerPoint…
SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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…

808 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