Formatting cells only with month and year

Options

Hi,

I use the following function

=DATE(YEAR([letzte Wartung]@row) + ROUNDDOWN((MONTH([letzte Wartung]@row) + Wartungsintervall@row) / 12; 0) + IF(IF(MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12) = 0; 12; MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12)) = 12; -1); IF(MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12) = 0; 12; MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12)); DAY([letzte Wartung]@row))

I want to show only month and year - is this possible ?

Best Answers

  • Joerg Schmidt
    Joerg Schmidt ✭✭✭
    Answer ✓
    Options

    Hi Paul,

    we leave everyting as it is. Thanks so much for your help ;-)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    To do this, you are going to want to use a formula that pulls the month

    =MONTH(date)


    enter your delimiter

    + "/"


    Then add in the year

    + YEAR(date)


    So

    =MONTH(date) + "/" + YEAR(date)

    becomes...

    =MONTH(DATE(YEAR([letzte Wartung]@row) + ROUNDDOWN((MONTH([letzte Wartung]@row) + Wartungsintervall@row) / 12; 0) + IF(IF(MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12) = 0; 12; MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12)) = 12; -1); IF(MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12) = 0; 12; MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12)); DAY([letzte Wartung]@row))) + "/" + YEAR(DATE(YEAR([letzte Wartung]@row) + ROUNDDOWN((MONTH([letzte Wartung]@row) + Wartungsintervall@row) / 12; 0) + IF(IF(MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12) = 0; 12; MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12)) = 12; -1); IF(MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12) = 0; 12; MOD(MONTH([letzte Wartung]@row) + Wartungsintervall@row; 12)); DAY([letzte Wartung]@row)))

  • Joerg Schmidt
    Options

    Hi Paul,

    last question to this issue:

    how can I roundup to the next month ? What do I have to change in the formula?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What is the criteria for rounding up to the next month? Did you want anything after the a specific date (such as the 15th) to go to the next month, or do you just want to add 1 to the month?

  • Joerg Schmidt
    Options

    the 15th would be a good criteria ;-)

  • Joerg Schmidt
    Joerg Schmidt ✭✭✭
    Answer ✓
    Options

    Hi Paul,

    we leave everyting as it is. Thanks so much for your help ;-)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!