Skip to main content
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 ...
Gautam Sheth's user avatar
  • 30.9k
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 ...
Mohamed El-Qassas MVP's user avatar
6 votes

Difference between two dates in hours

The formula for calculating hours between two times is =TEXT([Column2]-[Column1],"h")
Erin L's user avatar
  • 4,028
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 ...
Mike's user avatar
  • 12.2k
5 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.
Brian's user avatar
  • 57
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 '...
SP Developer's user avatar
  • 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 ...
Danny '365CSI' Engelman's user avatar
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([...
SharePointer's user avatar
  • 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 ...
Robin in Or's user avatar
  • 2,071
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 ...
Ganesh Sanap - MVP's user avatar
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 ...
Christoffer's user avatar
  • 9,821
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 ...
Hardik's user avatar
  • 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 ...
Suresh Bolineni's user avatar
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 "...
Alok Kumar Gupta's user avatar
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> &...
Robert Lindgren's user avatar
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!, #...
mannaggia's user avatar
  • 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" )
Karthik Jaganathan's user avatar
3 votes

How do I create a calculated conditional column using today's date, and automatically update each field daily?

Unfortunately, this isn't possible since Calculated Columns are not recalculated on viewing, but only on creation and modification. See this article: http://blog.pentalogic.net/2008/11/truth-about-...
theChrisKent's user avatar
  • 6,021
3 votes
Accepted

How to set Date Time Column

This works for me: =[Today]+14 And this is end result:
YogaPanda's user avatar
  • 859
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 ...
JLRishe's user avatar
  • 368
3 votes

How do I create a calculated conditional column using today's date, and automatically update each field daily?

Rather than a timer job which "touches" all the items, consider using jsLink or other client side rendering techniques to calculate the value in real time. Of course, it depends on how you are using ...
Marc D Anderson's user avatar
3 votes
Accepted

Date Column Validation - Must be 10th of Next Month

You can use below formula =IF(Day(DateField)=10, true, false)
Amal Hashim's user avatar
  • 28.3k
3 votes
Accepted

Is there a way to remove the time displayed in the scheduling end date column?

First option will be to go to the column settings and select Show Date only instead of Date and Time. If Settings not available you can have a calculated column in the view which takes only date from ...
Ankit Kumar's user avatar
  • 1,768
3 votes

Sharepoint 2013 - custom display form - time/date not formatted correctly

The date will save in the UTC format internally and on custom forms you have to format and show. If you are using jQuery for your custom form, you can format as follows (new Date("2017-03-30T13:00:...
Venkat Konjeti's user avatar
3 votes
Accepted

SharePoint Calculated column to Check If today is 14th date

Remember that SharePoint is NOT Excel, so Formulas do not update Date calculations How to use Today and Me in Calculated column Formula: =DAY( [DateTime] ) gets you the day number =IF( DAY( [...
Danny '365CSI' Engelman's user avatar
3 votes

How do I add hours to Now() after converting it to a text value?

As Now() returns a full formated date-time-stamp, I believe you should add hours by using divide a day by 24 hours by 60 minutes multiplied the amount of hours in minutes formula. In this case, it ...
moe's user avatar
  • 5,267
3 votes
Accepted

Calculated Date [Sharepoint Online]

You need an IF statement outside of the ISBLANK IF(ISBLANK([Closed Date]),"no date available", yourlongTEXTformattinghere)
Robin in Or's user avatar
  • 2,071
3 votes

Date value in SharePoint is one day off while retrieving

Use the SPTimeZone.UTCToLocalTime method to convert the UTC date time to local date time.
LZ_MSFT's user avatar
  • 6,219
3 votes
Accepted

Calculated Column Syntax nested DATEIF and ISERROR

It's clearer to test for blanks with ISBLANK(), not ISERROR(). DATEDIF is not needed for simple "d" math. Just subtract the dates. DATEDIF also raises an error if the result is negative. =[Date ...
Mike Smith - MCT's user avatar
3 votes

Calculated column comparing two dates

Something like this: =IF(ISBLANK([Published date]), "", IF([Modified]>[Published date], "Yes",""))
M.P.'s user avatar
  • 418

Only top scored, non community-wiki answers of a minimum length are eligible