Conditional Formatting

Hello ever one.

Have a nice day

Could you help me about the automation color in the cell when the valor is different above?

Like example attached.


thanks

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Adilson Mota

    I hope you're well and safe!

    Try something like this.

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Adilson Mota
    Adilson Mota ✭✭✭✭

    Thanks Andree. Very useful this information

  • Adilson Mota
    Adilson Mota ✭✭✭✭

    Hello

    I have a other challenge.


    What's the formula I could use in the Smartsheet like below and attached sheet?

    =SUMIFS(R:R,V:V,AJ3,S:S,">="&$AI$4,S:S,"<="&$AI$7)


    Thanks again and have a nice weekend

  • Adilson Mota
    Adilson Mota ✭✭✭✭

    Hello peaple.

    just sharing a formula to smartsheet that replaces from excel to smartsheet to add between data or month.


    Formula Excel = =SUMIFS(R:R,V:V,AJ3,S:S,">="&$AI$4,S:S,"<="&$AI$7)


    Formula Smartsheet = =SUMIFS({Gestão produção - Estamparia Range 1}, {Gestão produção - Estamparia Range 7}, [Descrição da Parada do processo]@row, {Gestão produção - Estamparia Range 8}, >=[1º dia da Semana - Atual]@row, {Gestão produção - Estamparia Range 8}, <=[Ultimo dia da semana - Atual]@row)


    Change ">="&Date for >=Date

  • Adilson Mota
    Adilson Mota ✭✭✭✭

    Hello

    Could you please help me identify the correct formula for last day in the month.

  • KPH
    KPH ✭✭✭✭✭✭

    There is not a function to find the last day in the month but you can write a formula using the DATE function, to calculate it. Because months have different lengths (28, 29, 30, 31 days), we need to find the start date of the subsequent month (which is always 1) and subtract on day to find the end date.

    For example, if you want to know the last day in the month based on a date in a cell called "Base Date" (formated as Date Type) your formula would be

    DATE(YEAR([Base Date]@row), MONTH([Base Date]@row) + 1, 1) - 1)

    If this formula is placed in another Date formatted column on the same row, it will return the last date of the month that Base Date is in.

    It does it by creating a date:

    • The year of which it takes from the Base Date cell. Let's say this was Jan 17th 2024. The year would be 2024.
    • The month it also takes from the Base Date, and it adds 1 to it. So for Jan 17th the month in the new date is February.
    • The day is always 1, i.e. Feb 1st.

    It then subtracts one from this date, and that gives us the last day in January.

    There is a slight complication in that you can't subtract 1 from the month of January to find December. 1-1 is not 12. So we add another little IF to return December 31st of the year in the Base Date cell, if the month is December.

    IF(MONTH([Base Date]@row) = 12, DATE(YEAR([Base Date]@row), 12, 31), DATE(YEAR([Base Date]@row), MONTH([Base Date]@row) + 1, 1) - 1))

    You don't need to output these dates into columns, you can use them within formula. You also don't need to have a start date cell, you could use TODAY() to find the end date of the current month.

    I hope this helps.

    I noticed you had a couple of questions here that were not answered - I think they are being missed as they will not appear in the "unanswered questions" list. When asking questions in the community, please mark helpful answers as accepted and start new threads for new questions. This will prevent your subsequent questions from being missed (they look like follow-ups rather than new questions) and help other users with similar questions to find answers.