Formatting cells only with month and year
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

Ok. I am going to very strongly suggest a slight change in the layout.
Is it an option for you to add in three helper columns that can be hidden after setup? We are going to have to use multiple IF statements to evaluate the date from the original formula, and keeping it all in one cell would very quickly become a monster. It can be done, but it is very much so NOT ideal.

Hi Paul,
we leave everyting as it is. Thanks so much for your help ;)

Haha. Happy to help! 👍️
Let me know if you change your mind. The basic idea is using an IF to initially look at the day, but then we would need to account for anything after 15 December. You wouldn't want to display a month number of 13, so we would use two more IF's to evaluate a little further and change 13 to 1 and add 1 to the year.
It's a pretty straightforward concept except that we are working with a date produced by an already complex formula.
Another option would be to leave the generated date somewhere on the sheet and use cell references in the above mentioned IF's to reference that column.
The "monster" only really happens if you are wanting to keep absolutely everything in a single column.
Answers

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

Hi Paul,
last question to this issue:
how can I roundup to the next month ? What do I have to change in the formula?

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?

the 15th would be a good criteria ;)

Ok. I am going to very strongly suggest a slight change in the layout.
Is it an option for you to add in three helper columns that can be hidden after setup? We are going to have to use multiple IF statements to evaluate the date from the original formula, and keeping it all in one cell would very quickly become a monster. It can be done, but it is very much so NOT ideal.

Hi Paul,
we leave everyting as it is. Thanks so much for your help ;)

Haha. Happy to help! 👍️
Let me know if you change your mind. The basic idea is using an IF to initially look at the day, but then we would need to account for anything after 15 December. You wouldn't want to display a month number of 13, so we would use two more IF's to evaluate a little further and change 13 to 1 and add 1 to the year.
It's a pretty straightforward concept except that we are working with a date produced by an already complex formula.
Another option would be to leave the generated date somewhere on the sheet and use cell references in the above mentioned IF's to reference that column.
The "monster" only really happens if you are wanting to keep absolutely everything in a single column.
Help Article Resources
Categories
Check out the Formula Handbook template!