I have created a calculated filed to convert a date field to another format, the issue I have is the calculated filed populate data for empty fields as well. Is there anyway it can only populate data for the filed which has a data. As you can see in the attachment, when there is empty cell it just fill it with 30/12/1899.

enter image description here

Kind Regards


Try this:

=IF(ISBLANK([date]), "", [date])

Test result:

enter image description here

  • thanks, so i need to add this as new Calculate field? – Harris Oct 26 '20 at 6:28
  • 1
    You can try to combine this with your original calculated formula, because you did not give the original calculated formula, I can only give this suggestion. – Amos Oct 26 '20 at 6:30
  • =TEXT(testdate,"dd,mm,yyyy") – Harris Oct 26 '20 at 6:33
  • 1
    =IF(ISBLANK([testdate]), "", TEXT(testdate,"dd,mm,yyyy")) – Amos Oct 26 '20 at 6:35
  • Thanks Amos, it works :) – Harris Oct 27 '20 at 1:54

Try this formula:

=IF(ISBLANK([Date Column]), "", TEXT([Date Column], "dd/mm/yyyy"))

Microsoft documentation: Calculated Field Formulas - Text Formats


  1. Put this formula in your calculated column (Review)
  2. Return your calculated field as Single line of text.
  3. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

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.