# Tag Info

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.9k
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.3k

### Difference between two dates in hours

The formula for calculating hours between two times is =TEXT([Column2]-[Column1],"h")
• 4,028

### 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

### 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.
• 57

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
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 ...
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
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,071
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 ...
• 45.5k
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,821

### 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
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

### 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 "...
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
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

### 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,848

### 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-...
• 6,021
Accepted

### How to set Date Time Column

This works for me: =[Today]+14 And this is end result:
• 859

### 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

### 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 ...
• 9,696
Accepted

### Date Column Validation - Must be 10th of Next Month

You can use below formula =IF(Day(DateField)=10, true, false)
• 28.3k
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 ...
• 1,768

### 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:...
• 4,959
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( [...

### 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 ...
• 5,267
Accepted

### Calculated Date [Sharepoint Online]

You need an IF statement outside of the ISBLANK IF(ISBLANK([Closed Date]),"no date available", yourlongTEXTformattinghere)
• 2,071

### 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.
• 6,219
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 ...
• 6,807