0

Three choice fields 1st route, 2nd route, 3rd route all containing text. And 1st route, 2nd route, 3rd route columns have values like: Dropdown values

Now I have three more columns:

1) Current Route (Calculated column checks above given choice columns and returns Single line text)

=IF(ISBLANK([3rd route]),IF(ISBLANK([2nd route]),IF(ISBLANK([1st route]),"",[1st route]),[2nd route]),[3rd route])

See formula for Current Route

2) Current Route Date (Calculated column checks Current Route and returns Date)

=IF(OR(ISBLANK([3rd route]),ISBLANK([3rd route date])),IF(OR(ISBLANK([2nd route]),ISBLANK([2nd route date])),IF(OR(ISBLANK([1st route]),ISBLANK([1st route date])),"",[1st route date]),[2nd route date]),[3rd route date])

See formula for Current Route Date

3) Problematic column -> Days Elapsed (Calculated column checks =IF(ISBLANK([Current Route Date]),0,TODAY()-[Current Route Date]) and returns Number)

or I have also tried

 =IF(OR(LEN([Current Route Date])=0,TODAY()-[Current Route Date]<0),0,TODAY()-[Current Route Date])

See formula for Days Elapsed

It always returns #Value! or #Name? when Current Route Date is empty which if you see the formula it returns "" and which if I am not wrong stores "01/01/1900" in Current Route Date column. So why doesn't ISBLANK() succeeds in checking Current Route Date? See List View

akg1421
  • 51
  • 12

3 Answers3

2

Per my test, I could reproduce the issue on my end. As a workaround, we could use this formula instead:

=IF([Current Route Date]="",TODAY()-[Current Route Date])
Michael Han
  • 3,475
  • 1
  • 6
  • 8
  • It is not working for me. I have also tried with `=IF(OR(LEN([Current Route Date])=0,TODAY()-[Current Route Date]<0),0,TODAY()-[Current Route Date])` – akg1421 Sep 26 '19 at 10:17
  • I have added in the formulas in question... edited my post. Please see! – akg1421 Sep 26 '19 at 10:24
1

Michael Han has the right answer. As to why your examples did not work:

 

The OR is testing both conditions, and the second fails as "" is not a date.

So instead of:

=IF(OR(LEN([Current Route Date])=0,TODAY()-[Current Route Date]<0),0,TODAY()-[Current Route Date])

Try:

=IF(LEN([Current Route Date])=0,
   0,
   IF(TODAY()-[Current Route Date]<0,
      0,
      TODAY()-[Current Route Date]))

As to the ISBLANK, "" is not blank, it is a value.

Try changing: 

=IF(ISBLANK([Current Route Date]),0,TODAY()-[Current Route Date])

to

=IF([Current Route Date]="",0,TODAY()-[Current Route Date])
Mike Smith - MCT
  • 1,166
  • 1
  • 9
  • 15
1

Validation formula to enforce either null or today’s date in a date field

=IF(OR(ISBLANK(DATE_VAL)=TRUE,DATE_VAL=TODAY()),TRUE,FALSE)

tested and works fine

Daemon Painter
  • 3,208
  • 3
  • 29
  • 44
Parwaze Sadool
  • 11
  • 1