16
votes
Accepted
CAML query compare DateTime in UTC
Modify your CAML Query to <Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE'>2018-06-28T07:07:09Z</Value>
Here, we have added the StorageTZ=TRUE attribute which tells ...
- 30.8k
15
votes
Accepted
CAML query format filter in DateTime column
You need to format the date like this:
Server Site
string date = SPUtility.CreateISO8601DateTimeFromSystemDateTime(inputDate);
Sample for your case:
inputDate = new DateTime(2016,04,18);
string ...
13
votes
Accepted
Time Difference Formula in Calculated Column
Try the following formula
=TEXT(Created-Closed,"h")
[Output]
In case you need to show the exactly Hours and minutes, try this
=TEXT(Created-Closed,"hh:mm")
[Output]
Regarding Days and Hours and ...
- 45.4k
10
votes
Accepted
Calculated column increase date column by one day
Date columns are essentially numbers starting at 30/12/1899
So besides adding one day, you have to display it as a date again (when using it in a Calculated Column datatype=Text)
=TEXT( [Due Date]+1 , ...
- 21.2k
7
votes
Difference between two dates in hours
The formula for calculating hours between two times is =TEXT([Column2]-[Column1],"h")
- 4,038
6
votes
Create a Current Date and Time Variable in SharePoint Designer 2013
I've found that in Sharepoint Designer 2013 there's an option to select today. If you click the ellipses next to the value option, there's a selector for current date.
- 67
6
votes
Accepted
How to use powershell to change a datetime field to "Date Only"?
It's fairly straight forward I would say.
// First get your field using the title from your list
$field = $list.Fields["FieldTitle"]
// Alternativly you could use $list.Fields.GetFieldByInternalName(...
- 619
5
votes
Accepted
Set default value of Article Start Date to [Today] with CSOM
This is possible for ArticleStartDate on O365 but involves modifying the SchemaXml (which isn't the nicest solution I appreciate, but needs must). Note that the below code snippet is in C# but ...
- 198
5
votes
Is there a way to make a workflow run every Monday?
I wouldn't do this with workflow, I would do this in a PowerShell script that runs on the server, set it up in a Task Scheduler, and let it run every Monday. The script would kick off the workflow ...
- 12.2k
5
votes
Can hours be added to a Task List?
You can achieve this by changing Date and Time Format of Start Date & Due Date columns.
Go to Task list setting.
Edit column Start Date/Due Date.
Change Date and Time Format from Date only to '...
- 3,085
5
votes
Accepted
Sum of two Time columns
Date calculations go from 23:59 to 00:00
So
=TEXT( Time(16,35,0)+Time(14,0,0) , "hh:mm" )
will produce
06:35
Let's take different values to examine edge-cases
What should 16:35 + 14:35 do?
To ...
- 21.2k
5
votes
Accepted
How to calculate Days, Hours and minutes between two different dates and times
Try this:
=CONCATENATE(INT([Date 2]-[Date 1])," Days ",TEXT([Date 2]-[Date 1],"h"" hrs ""m"" mins """))
More information here
if you want only days, month year then this:
=CONCATENATE(DATEDIF([...
- 3,046
5
votes
Accepted
Add 2 hours to calculated Date and Time field
Well, you can add days to that by coding =NOW()+2 That gives you 2 days from now. It expects the number to be days. To add hours, do
=NOW()+(2/24)
Or you can change the 2/24 to 1/12, but I usually ...
- 2,081
5
votes
Accepted
Calculated Column Based off of a Date & Time Field
Create a new calculated column & use formula in below format:
=DATE(YEAR([Onboarding Date]) + 1, MONTH([Onboarding Date]), DAY([Onboarding Date]))
Documentation: Calculated Field Formulas - Date ...
- 42.8k
4
votes
Accepted
Display current year on 2013 list
This is because Date is by default shown in Friendly format. One quick workaround you can do is to go to List Settings--> Click on Modified --> Change Display Format to Standard . Then dates ...
- 8,809
4
votes
Adding month to calculated value
I presume you are on an english environment, so the separator is , (comma) and not ; (semi-colon)
=DATE(YEAR([Last updated]), MONTH([Last updated])+3, DAY([Last updated]) )
More functions and syntax: ...
- 21.2k
4
votes
Accepted
How can I convert the format ctx.CurrentItem.Created
Someone suggested the MomentJS library, which is great for doing Date calculations and transformations.
But 2015/10/20 PM 06:09 is an invalid Date notation so MomemtJS won't make it valid either.
I ...
- 21.2k
4
votes
Accepted
Display datepicker on top of the forms's modal dialog
I did try to add 100 pixels to the modal dialogs height through jQuery once one of the calendar images are clicked (to open the datepicker) and then disable the jQuery to prevent any more height to be ...
- 9,721
4
votes
Calculated field for difference between two times
When you subtract a date field from another date field in a calculated column, the result is given in days.
You can therefore multiply the difference by 24 to get the result in hours, so your formula ...
- 1,620
4
votes
workflow 2010 will show a Date equal to 01/01/0001 inside the email, if the Date is empty
Date fields are never truly empty, so the option doesn't exist. An "empty" date field is actually set to 1/1/0001.
So for your specific project, forget the whole "storing the string values of the ...
- 7,733
4
votes
Accepted
Whats wrong in given caml query?
It works for my list. You just check the internal name of the fields StartDateTime and EndDateTime and date format mention in the value section in query 2017-03-23T12:00:00Z
I think you mention Le ...
- 1,518
4
votes
Whats wrong in given caml query?
The operators "Le" & "Ge" are not valid. It needs to either "Lt"/"Gt" (Less than/Greater than) OR "Leq"/"Geq" (Less than or Equal/Greater than or equal").
Also, it may be that, one would use "...
- 593
4
votes
Accepted
Query the year from a date field in CAML Query
Your ending </Eq> tag is misplaced. It should be placed before the <Contains> tag. Also some of your escapings are incorrect.
<Query>
<Where>
<And>
&...
- 24.5k
4
votes
Accepted
How do you handle "#NUM!" in a SharePoint calculated field?
Use the ISERROR() function.
=IF(ISERROR(DATEDIF(NOW(),[Proposal Status Date],"d")),1,2)
ISERR: Value refers to any error value except #N/A.
ISERROR: Value refers to any error value (#N/A, #VALUE!, #...
- 999
4
votes
Calculated column to show status if another has a value
try the below formula, add the below formula in the status - calculated column
=IF( ISBLANK([completed]) , "Open" , "Closed" )
- 7,838
3
votes
How do I format a Date for my local region in a customised SharePoint Display Template
If you retrieve a managed property of type "Date and Time" it will present you a Date object with the timezone of your local pc.
Here is an example of how I retrieve my dates in my timezone:
If I ...
- 1,693
3
votes
REST API filter by start and end dates using 'today'
I was pulling the data for online Share-point 2013/ office 365. I used the above method. But It did not return the desired result. I had created the two sample record on with date "2 april 2016". ...
- 31
3
votes
SharePoint Designer - How to get current server datetime through JavaScript REST?
The accepted answer here is wrong because it does not account for DST (daylight savings time).
Using _spPageContextInfo also doesn't always work because that's not necessarily populated (especially ...
- 368
3
votes
Display current year on 2013 list
Not an answer, just extra info when working with past dates
=TEXT([Modified],"YYY")
In an extra Calculated Column Year gets you the Year only,
You can then use that in View Filters, Sorts or Group ...
- 21.2k
3
votes
Calculated field for difference between two times
Change your calculation to
=24*(start-end)
and set the column type to Number with 1 decimal.
- 9,721
Only top scored, non community-wiki answers of a minimum length are eligible