Time Calculation

Viewing 3 reply threads
  • Author
    Posts
  • June 7, 2020 at 6:58 AM #40834

    Patrice Hof
    Participant

    Hi,
    Here is my problem with time calculation:

    Field 1 = Date 1
    Field 2 = Date 2
    Field 3 = Checkbox

    Field 4 = duration if checkbox is ticked (=age at date 2)
    Field 5 = duration until today if checkbox is not ticked (=age today)

    Here are my formulas:

    Field 4
    IFNOTEMPTY(CHECKBOX;YYMM(DATE1;DATE2);NULL)
    Returns=Text
    Expected result if checkbox is ticked: 4 years, 2 months ==> OK
    Expected result if checkbox is not ticked: blank ==> Not OK: the field displays -44 years, -2 months

    Field 5
    IFEMPTY(CHECKBOX;YYMM(DATE1;TODAY());NULL)
    Returns=Text
    Expected result if checkbox is ticked: blank ==> OK
    Expected result if checkbox is not ticked: 12 years, 10 months ==> Not OK, the field remains blank

    Could you explain what I am doing wrong?
    Thank you very much
    patrice

    June 7, 2020 at 9:12 AM #40835

    Daniel Leu
    Participant

    I think checkbox contains either value 0 or 1. So it is always not empty. Try to do a value comparison instead.

    June 7, 2020 at 10:30 PM #40840

    Brendan
    Keymaster

    Hi Patrice,

    As Daniel suggested correctly, a checkbox contains 0 or 1.

    So instead of IFEMPTY, use IF.

    For example:

    IF(CHECKBOX = 1; YYMM(DATE1; DATE2); "")

    June 8, 2020 at 10:52 AM #40848

    Patrice Hof
    Participant

    Works fine
    thank you Daniel and Brendan

Viewing 3 reply threads

You must be logged in to reply to this topic.