Cannot calculate future date based on two source fields.

04/19/21
Accepted

When adding a set of days to a historical date usually that will give you a future date example:

=[Sale date]@row + [Delivery days]@row will give me a future date

When using Data mesh or Vlookup to bring the two source fields to another reference smartsheet, the delivery days will add to the year instead of the days, thus only increasing the "years" and not months nor days.

I have tried

=Date(Year([Sale date]@row), Month([Sale date]@row), Day([Sale date]@row) + [Delivery days]

=Date(Year([Sale date]@row), Month([Sale date]@row), Day([Sale date]@row + [Delivery days]

Please help!

Popular Tags:

Best Answers

  • Accepted Answer

    Hi Paul,

    Yes they are date fields, I've also noticed an ' in the MLA Calc field where days to be added

    So I would like to return a date based on RTB expiration + MLA Calculation in a date field, all I get is unparsable.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    There's the issue. The apostrophe is converting the numbers to text values. See if this works...

    =[RTB Exp Date]@row + VALUE([MLA Calc]@row)

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What is the VLOOKUP formula?


    It sounds like your dates are actually being stored as text strings that only look like date values. Have you double checked to ensure that the column is a date type column?

  • Accepted Answer

    Hi Paul,

    Yes they are date fields, I've also noticed an ' in the MLA Calc field where days to be added

    So I would like to return a date based on RTB expiration + MLA Calculation in a date field, all I get is unparsable.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    There's the issue. The apostrophe is converting the numbers to text values. See if this works...

    =[RTB Exp Date]@row + VALUE([MLA Calc]@row)

  • Thanks, it worked.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.