Formatting cells only with month and year

Joerg Schmidt

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 ?

Paul Newcome

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.

Joerg Schmidt

Hi Paul,

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

Paul Newcome

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.

Paul Newcome

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

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

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

the 15th would be a good criteria ;-)

Paul Newcome

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.

Joerg Schmidt

Hi Paul,

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

Paul Newcome

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.