Discussions

Expand all | Collapse all

Help with calculating a date with formula fields

  • 1.  Help with calculating a date with formula fields

    Top
    Contributor
    Posted 07-27-2017 13:31
    This is the first semi-complicated formula I have tried and it is not working!

    I have 2 fields that need to be involved in the calculation of Field #3

    Field #1 - Date field
    Field #2 - Numeric field
    Field #3 - Formula - date (should it be something else?)

    What I need Field #3 to calculate is this:
    Field #1 + (Field #2 * 7)

    This seems so simple, but I keep getting errors about mismatched types. I don't understand why Quickbase isn't smart enough to just add a number of days to a date.

    Help!!!!!!!!


  • 2.  RE: Help with calculating a date with formula fields

    Bronze
    Contributor
    Posted 07-27-2017 13:43
    [Field1] + Days([Field2]*7)

    Your formula should work if you change Field2 from numeric to duration


  • 3.  RE: Help with calculating a date with formula fields

    Top
    Contributor
    Posted 07-27-2017 14:35
    Now I am getting this error message:

    The types of the arguments or the number of arguments supplied do not meet the requirements of the function Days.

    The function is defined to be Days (Number n).


  • 4.  RE: Help with calculating a date with formula fields

    Bronze
    Contributor
    Posted 07-27-2017 14:38
    What field types do you have now?
    Field #1 - Date field
    Field #2 - Numeric field
    Field #3 - Formula - date
    If its like your original post then the formula date should be this
    [Field1] + Days([Field2]*7)

    If its like this
    Field #1 - Date field
    Field #2 - Duration field
    Field #3 - Formula - date

    formula would be this
    [Field1] + ([Field2]*7)


  • 5.  RE: Help with calculating a date with formula fields

    Top
    Contributor
    Posted 07-27-2017 15:00
    I thought that solved it, but when I actually tried it with values it is calculating completely incorrectly.

    The fields are:
    Field #1 - Date field
    Field #2 - Duration field
    Field #3 - Formula - date

    The formula is:

    [Field #1] + ([Field #2]*7)

    The Duration field is representative of weeks (1 week, 2 weeks, etc.)

    This formula is supposed to take a start date and calculate a due date that is X weeks later.

    The Duration field has a value display dropdown option for weeks, which is what I selected (although I'm not sure what affects). This seems like it should work, I am at a loss as to why it does not.

    Also, I really appreciate your help!


  • 6.  RE: Help with calculating a date with formula fields

    Bronze
    Contributor
    Posted 07-27-2017 15:05

    [Date] + ([Add Weeks]*7)

    Does the formula value look correct? If you just want to add how many weeks are entered in the field then  [Date] + ([Add Weeks])


  • 7.  RE: Help with calculating a date with formula fields

    Top
    Contributor
    Posted 07-27-2017 15:22
    Obviously September 5 is not 1 week after July 27, so that formula didn't calculate correctly?


  • 8.  RE: Help with calculating a date with formula fields

    Bronze
    Contributor
    Posted 07-27-2017 15:31
    Right because there was the *7 in the formula below the picture, thats why I said if you just wanted to add the number of weeks thats entered in the box, just use [Date] + [Add Weeks]



    [Date] + [Add Weeks]

    I suggest starting the question with what the end result should be as oppose of technical questions about field type issues in a formula.

    Hope this helps resolve your issue! 


  • 9.  RE: Help with calculating a date with formula fields

    Top
    Contributor
    Posted 07-27-2017 15:38
    I guess I was trying to make it more complicated than it needed to be! Changing that Duration field to read as weeks makes the *7 redundant, which seems very obvious in hindsight.

    I'm always hesitant to be specific about what my form fields are for in a question, as we all use Quickbase and it's functions for very different things.

    Thanks so much for all your help, it's working perfectly now!


  • 10.  RE: Help with calculating a date with formula fields

    Bronze
    Contributor
    Posted 07-27-2017 15:46
    Completely understandable! If you're feeling generous and want to show support, tips are deeply appreciated and help me, help other people along the way! Take care Chelsea I hope you have a great weekend!

    chuck.support/donate

    Feel free to reach out directly if you have questions or get stuck on a problem.
    chuck@chuck.support