1

I am struggling to find an answer on how to format a If then statement in SharePoint 2013 using a calculated column. I need to display the word "Completed" if the date =1/1/1900 or display the date itself

Current Code:

  =IF( [GE Internal Report Receipt]="1/1/1900"
         ,"Complete"
         ,TEXT([GE Internal Report Receipt],"dd/MM/yyyy")
     )

Currently this displays the date 1/1/1900 in the date format. This should say complete. I am finding if I do a standard If statement =IF([GE Internal Report Receipt]="1/1/1900","Complete",[GE Internal Report Receipt] I get an output of 2 for the date 1/1/1900 and an output of a random number if there is a true date in the field.

Current Code: Almost working with ISBLANK

=IF(ISBLANK([GE Internal Report Receipt]),"Missing Date")

=If([GE Internal Report Receipt]=DATE(1900,1,1)
    ,"Complete"
    ,Text([GE Internal Report Receipt],"dd/MM/yyyy"
   )
Improve this question
2
  • See my answer below. where it combines it into one Formula. You can't declare two Formulas with = into one. Best method is to create your Formulas in Notepad with linebreaks and copy/paste them into SharePoint (SP will take all linebreaks and spaces out) Commented Jan 20, 2017 at 19:36
  • PS. When you paste code in StackOverlfow start each line with 4 spaces/or a Tab, that will format them as code instead of text Commented Jan 20, 2017 at 19:40

2 Answers 2

Reset to default
3

You have to go the other way around,

Not convert your date to Text but convert the text you use to compare to a Date.

That will also make it safe to use in any language

provided your field IS a Date field

IF( [GE Internal Report Receipt] = DATE( 1900 , 1 , 1 )  
   ,"Complete"
   ,TEXT([GE Internal Report Receipt],"dd/MM/yyyy")
 )

Note that SharePoint Dates are actually numbers starting counting at december 31st 1899
It is the UI/Language settings that converts them to Date representations

=TEXT( 1 , "dd/MM/yyyy" )

will output 31/12/1899 

january 1st 1900 = 2
january 2nd 1900 = 3

etc.

So that Random number you mentioned is the number of days since December 31st 1899

So you could do

IF( [GE Internal Report Receipt] = 2  
   ,"Complete"
   ,TEXT([GE Internal Report Receipt],"dd/MM/yyyy")
 )

But no-one opening up this Formula will understand whats going on

This is also why BLANK dates are 0 in calculations representing 30 December 1899
To test for blank dates you have to use:

IF( ISBLANK( [GE Internal Report Receipt] ) 
   ,"Missing date"
   IF( [GE Internal Report Receipt] = DATE( 1900 , 1 , 1 )  
       ,"Complete"
       ,TEXT([GE Internal Report Receipt],"dd/MM/yyyy")
     )
 )

All available functions in SharePoint: http://www.viewmaster365.com/365coach/#/Calculated_Column_Functions_List

Improve this answer
7
  • Danny, This worked great however it has now created a new issue. If the field [GE Internal Report Receipt] is blank, I need the other field to be blank. We use this column for counting. Great explanation by the way. Commented Jan 20, 2017 at 19:06
  • see the ISBLANK function to test for blanks.. and then you also might need the AND function to test for multiple conditions Commented Jan 20, 2017 at 19:08
  • I made a mistake, corrected the explanation to explain day 1 is the last day of 1899 Commented Jan 20, 2017 at 19:14
  • Danny, Both pieces are working on their own, however they are not working together. Any thoughts? Commented Jan 20, 2017 at 19:30
  • Add your Formulas into your first question Commented Jan 20, 2017 at 19:30
1

More than likely the two objects you are comparing in the IF statement are not equal because one is a date the other is text. Try this:

IF(TEXT([GE Internal Report Receipt], "dd/mm/yyyy")="1/1/1900","Complete",TEXT([GE Internal Report Receipt],"dd/MM/yyyy"))

That should make the date object into text so the comparison is apples to apples.

Improve this answer
7
  • Sharepoint took the string, however it did not work out just yet. I plugged that in and ended up with the date 1/1/1900 for a final output. I am wondering if there is something wrong with the part ,"complete", since it should be the true value and this is taking the false value. Commented Jan 20, 2017 at 18:35
  • I have verified it is officially pulling the false value and not just pulling the date ahead. The [GE Internal Report Receipt] column Commented Jan 20, 2017 at 18:42
  • What if I were to reformat the [GE Internal Report Receipt] column for a default value of text? Would that do the trick? If so what is that string, what I am putting in doesnt seem to take. Commented Jan 20, 2017 at 18:43
  • One thing you might double check is if the [GE Internal Report Receipt] is a date column that is formatted dd/mm/yyyy. The next would be to just output the text value for the column (e.g. TEXT([GE Internal Report Receipt], "dd/mm/yyyy") ) just to see what the text value is. Commented Jan 20, 2017 at 18:46
  • What do you mean by false value? Commented Jan 20, 2017 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.