How can i have a cell that shows an amount from another cell if its 30days past a date in another ce

02/28/21
Accepted

row 1 date billed

row 2 dollar amount

row 3 dollar amount past 30 days

Row 4 dollar past 60 days

Row 5 Dollar past 90 days

=if( row 1 is less then 30 days) show row 2

=if( row 1 is less then 60 days but greater then 30 days) show row 2

=if( row 1 is less then 90 days but greater then 60 days) show row 2

=if( row 1 is greater then 90) show row 2

Best Answers

Answers

  • Antonio FigueroaAntonio Figueroa ✭✭✭✭

    Try this

    Set the columns up Date Billed - Amount - Amount Past 30 Days - Amount Past 60 days - Amount Past 90 Days - Current Amount. Then in Current amount use the nested if formula Below.


    =IF(TODAY() >= [Date Billed]@row + 90, [Amount Past 90 days]@row, IF(TODAY() >= [Date Billed]@row + 60, [Amount Past 60 days]@row, IF(TODAY() >= [Date Billed]@row + 30, [Amount Past 30 Days]@row, [email protected])))

  • wpfmbswpfmbs ✭✭✭✭

    That would work if i was posting the individual amounts to each of the date columns. what im looking for is the info get pulled from the "amount" column you have and is transposed to the correct column of 30, 60, 90 compared to todays date.

  • Antonio FigueroaAntonio Figueroa ✭✭✭✭

    I think I understand what you want give me a few and I will send you a link

  • Antonio FigueroaAntonio Figueroa ✭✭✭✭
    Accepted Answer

    Please see the below link and let me know if this works.

    Please let me know when you are done with it so I can delete it off my account.

    thank you


    https://app.smartsheet.com/b/publish?EQBCT=04bcbec469594b799ef261e0554f9466

  • wpfmbswpfmbs ✭✭✭✭

    awesome your solution worked!

    Column 1 amount

    column 2 dated billed

    column 3 helper

    =IF(TODAY() >= [Date Billed]@row + 90, "90 Days Past Due", IF(TODAY() >= [Date Billed]@row + 60, "60 Days Past Due", IF(TODAY() >= [Date Billed]@row + 30, "30 Days Past Due", "")))

    Column 4 30 days past

    =IF([Helper Column]@row = "30 Days Past Due", [Primary Column]@row, "")

    Column 5 60 days

    =IF([Helper Column]@row = "60 Days Past Due", [Primary Column]@row, "")

    Column 6 90 days past

    =IF([Helper Column]@row = "90 Days Past Due", [Primary Column]@row, "")

  • Antonio FigueroaAntonio Figueroa ✭✭✭✭
    Accepted Answer

    Glad I can Help.

Sign In or Register to comment.