Forum Discussion

SyaefulBahri3's avatar
SyaefulBahri3
Qrew Trainee
6 years ago

Duration Formula with If Statement

Hi All,

I have three kind of recruitment type : direct, open and restricted. 
Open recruitment must be following all the step from advertisement to contracted
I can count how many days for the process by [contracted date] - [advertisement date]

The problem is with above formula it can not count how many days for direct and restricted date because this two type is not following all the step.

I can achieve this by creating 3 different report with 3 different formula for each recruitment type

Restricted recruitment: [contracted date] - [selection panel date]
Direct recruitment: [contracted date] - [interview/assessment date]

It possible to create just one report for this case, probably with if statement?. How can I achieve this?

Many thanks

------------------------------
Syaeful Bahri
------------------------------
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    if(
    [Recruitment Type] = "open", [contracted date] - [advertisement date],
    [Recruitment Type] = "direct", [contracted date] - [interview/assessment date],
    [Recruitment Type] = "restricted", [contracted date] - [selection panel date]
    )

    If you want it to have some kind of duration if there is no status you would do it like this.

    if(
    [Recruitment Type] = "open", [contracted date] - [advertisement date],
    [Recruitment Type] = "direct", [contracted date] - [interview/assessment date],
    [Recruitment Type] = "restricted", [contracted date] - [selection panel date],
    [other] - [duration]
    )

    You are going to want a formula duration field.
    • SyaefulBahri3's avatar
      SyaefulBahri3
      Qrew Trainee
      6 years ago
      This is working for me to achieve my purpose

      if(
      [Recruitment Type] = "open", [contracted date] - [advertisement date],
      [Recruitment Type] = "direct", [contracted date] - [interview/assessment date],
      [Recruitment Type] = "restricted", [contracted date] - [selection panel date]
      )

      I'm curious about what do you want to achieve with the second formula?. Because it's show error for [other] - [duration], [other] is not recognize 

      Can you please explain it to me?

      Many thanks

      ------------------------------
      Syaeful Bahri
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        6 years ago
        Basically if none of the options are true it will just be blank. With the second formula it will display the very last condition instead. So if everything comes up false you will still be able to show a duration there. Something like [Date Created] - Today() would work since it will tell you the time the thing was open and had no status assigned to it. If your Recruitment Type field is required then you wouldn't really need the last false condition since there would always be a status (open, direct, restricted) on each record.

        If that doesn't make sense let me know.