0

I Need the Right Formula for the following Problem:

I got a column that contains only date but sometimes it contains no Information.

=DAY([AB])&"."&MONTH([AB])&"."&YEAR([AB])

This represents the date from column AB in the Right way, but if there is no Information, the result is 01.01.1899.

What I Need is an IF Formula like:

If the column [AB] contains information -> represent it in the following way dd.mm.yyyy else "-" .

Maybe someone can help !

This is the data that already exist in the Date column:

enter image description here

  • Please show your formula (copy and paste here) – Raf Jan 13 at 10:44
  • =IF(ISBLANK([X-first input]), "-", DAY([X-first input])&"."&MONTH([X-first input])&"."&YEAR([X-first input])) – Niklas Jan 13 at 10:47
  • Please try to use column name without spaces and '-'. For a test – Raf Jan 13 at 10:50
  • Same result…. I think the Column Name has to be with Space, cause the original one hast a space too =IF(ISBLANK([X-firstinput]), '-', DAY([X-firstinput])&"."&MONTH([X-firstinput])&"."&YEAR([X-firstinput])) this was what i tried – Niklas Jan 13 at 10:58
  • I mean try to rename original column name. – Raf Jan 13 at 10:58
0

Assuming, your AB column is of type Date & Time and you have not set any default value to your column.

Try using below formula:

=IF(ISBLANK([AB]),"-",DAY([AB])&"."&MONTH([AB])&"."&YEAR([AB]))

Note:

  1. 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(,).
  2. Return your calculated field as Single line of text.
  3. IF you have spaces in your column then wrap it inside square brackets like [My Column Name With Spaces].

References:

  1. IS functions - ISBLANK.
  2. Calculated Field Formulas.
| improve this answer | |
0

Try this:

=IF(ISBLANK([AB]),"-",DAY([AB])&"."&MONTH([AB])&"."&YEAR([AB]))

enter image description here enter image description here

| improve this answer | |
  • Dear @Niklas please mark as an answer if my answer was helpful. – Raf Jan 13 at 10:30
  • Is your CALC2 column a returned as 'a single line of text' ? I tried and I got 'Sorry, something went wrong' – Niklas Jan 13 at 10:32
  • @Niklas yes. I.ve updated my answer. – Raf Jan 13 at 10:33
  • @Niklas Yes, it has to return the type as "Single line of text" . – Ganesh Sanap Jan 13 at 10:35
  • @Niklas please update your question with screenshots – Raf Jan 13 at 10:38

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.