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"
   )
  • 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) – Danny '365CSI' Engelman Jan 20 '17 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 – Danny '365CSI' Engelman Jan 20 '17 at 19:40
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

  • 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. – Cory Cox Jan 20 '17 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 – Danny '365CSI' Engelman Jan 20 '17 at 19:08
  • I made a mistake, corrected the explanation to explain day 1 is the last day of 1899 – Danny '365CSI' Engelman Jan 20 '17 at 19:14
  • Danny, Both pieces are working on their own, however they are not working together. Any thoughts? – Cory Cox Jan 20 '17 at 19:30
  • Add your Formulas into your first question – Danny '365CSI' Engelman Jan 20 '17 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.

  • 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. – Cory Cox Jan 20 '17 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 – Cory Cox Jan 20 '17 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. – Cory Cox Jan 20 '17 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. – MonkeyWrench Jan 20 '17 at 18:46
  • What do you mean by false value? – MonkeyWrench Jan 20 '17 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.