# 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 ?

Options

Hi Paul,

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

• ✭✭✭✭✭✭
Options

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

=MONTH(date)

+ "/"

+ 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)))

• 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?

• ✭✭✭✭✭✭
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?

• Options

the 15th would be a good criteria ;-)