sullisnyc44
asked on
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...
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...
You could try =DATE(YEAR([Start Time]),MONTH([Start Time] + 1),DAY([Start Time]))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I'm not 100% sure that this behavior is actually consistent for each item - I must examine the data thoroughly.
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.
ASKER
ah. ok I will just confirm that the behavior is consistent. Thanks for the response.
ASKER
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
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
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
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
ASKER
Thanks for the response.
So my formula would be:
=date(text([StartTime])
?
So my formula would be:
=date(text([StartTime])
?
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",[Sta rt 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
Here is the formula i would use to get StartTime:
=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Sta
i used the examples here:
http://office.microsoft.com/en-gb/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx
ASKER
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.
[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.
ASKER
Here are some of my results when I attempt to display the Date as text.
StartDateTESTDay=DAY([Star t 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
StartDateTESTDay=DAY([Star
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
ASKER
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",[Sta rt Time]+1,[Start Time])
=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Sta
ASKER
This is what worked - thanks for the answer!
=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Sta rt Time]+1,[Start Time])
=IF(TEXT([End Time]-[Start Time],"h:mm")="23:59",[Sta
Glad to hear it worked; I will send the "thank you" to my colleague who told me the "23:59" trick :D