IF Formula

[alhazen]

Hey guys,

I need your help. I have two tables which are "assessment" and "issue". "assessment" has five column which are [id], [issue_id], [start_date_assessment], [user_department], [any_issue]. Meanwhile "issue" has seven column which are [id], [user_department], [assessment_planned_date], [start_date_assessment], [date_issue_raised], [date_issue_closed], and [issue_cycle_time]. 

i use this formula (HOUR([date_issue_closed] - [date_issue_raised]) / 24) for [issue_cycle_time] column.

I want the formula of [issue_cycle_time] to active when [any_issue?]="Yes".  Does it make sense?

Thank you very much for your help.

[SkrOYC]

If [any_issue?] is a Yes/No column, replace your [issue_cycle_time] expression with:

IF(
  [any_issue?],
  Your_Current_Expression,
  ""
)

[alhazen]

Thank you for reply. I think it doesn't work because [any_issue] the column of "assessment" table.

[SkrOYC]

Ok. It souns like it doesn't make sense to me.

How is [any_issue?] receiving the data? Manually or via expression?

[alhazen]

[any_issue] data entered manually.

hey @Steve do you have an idea for my case?

[Steve]

What are you trying to accomplish with all this? What is the point of the issue_cycle_time column value?

[alhazen]

Hi @Steve thanks for the reply

When the user fills the [any_issue] field with "yes", the user will record the date the problem was found in the [date_issue_raised] column and the problem was resolved in [date_issue_closed], so [issue_cycle_time] will automatically calculate how many days to resolve the issue.

However, When user fills [any_issue] field with "no", [date_issue_raised] and [date_issue_closed] will autofill with today(), and [issue_cycle_time] is zero.

Can we do that with IF formula?

[Steve · Accepted solution]

date_issue_raised

Initial value:

TODAY()

date_issue_closed

Valid If:

IF(
  ("yes" = [any_issue]),
  ([_THIS] >= [date_issue_raised]),
  ([_THIS] = [date_issue_raised])
)

Initial value:

[date_issue_raised]

Editable?:

OR(
  ISBLANK([_THIS]),
  ("yes" = [any_issue]),
  ([_THIS] <> [date_issue_raised])
)

issue_cycle_time

App formula:

IF(
  ("yes" = [any_issue]),
  ([date_issue_closed] - [date_issue_raised]),
  0
)

[alhazen]

Perfect, thank you so much @Steve 

I was using this formula 

ifs(
and(ISNOTBLANK([date_issue_closed]),
"Yes"=any(select(assessment[issue],([schedule]=[_thisrow].[id],true)))),
HOUR([date_issue_closed] - [date_issue_raised]) / (24)+(1),
and(ISNOTBLANK([date_issue_closed]),
"No"=any(select(assessment[issue],([schedule]=[_thisrow].[id],true)))),
NUMBER("0")
)