# MINUS ONE YEAR FROM DATE IF MONTH IS GREATER THAN CURRENT MONTH.

#### kv.singh

##### New Member
Currently I am using excel formula to find 01st date of next month. I want excel formula for the following condition:-
If month shown in D2 is greater than current month then year shown in date should reduce by 1 year. and if month shown in D2 is <= current month then
show current year .

#### Attachments

• 9.4 KB Views: 6

#### GraH - Guido

##### Well-Known Member
Hi kv.singh, one way
=IF(0+MID(A3,5,2)<MONTH(TODAY()),EOMONTH(TEXT(A3,"0000\/00\/00"),-12)+1,EOMONTH(TEXT(A3,"0000\/00\/00"),0)+1)

But bear with, smarter, shorter solutions are probably on the way.

#### Attachments

• 9.9 KB Views: 9

#### kv.singh

##### New Member
WHAT CHANGE I HAVE TO MADE IN FORMULA IF I DON'T WANT TO ADD ONE MONTH.(REFER NEW ATTACHMENT)

#### Attachments

• 9.6 KB Views: 6

#### Peter Bartholomew

##### Well-Known Member
Hi Guido
I wasn't convinced by the idea of comparing month numbers without taking the year into consideration. My formula for returning the 1st of the month is drawn from your idea of reformatted text but introduces "/01" for the day. I think you now have access to dynamic array formulas so, just to be annoying, this takes the formula on a further (unnecessary?) step!
Code:
``````= LET(
format, "0000\/00",
thisMonth, EOMONTH(TODAY(), -1) + 1,
startMonth, --(TEXT(LEFT(encodedDate, 6), format) & "/01"),
isFuture?, startMonth > thisMonth,
IF(isFuture?, EDATE(startMonth, -12), startMonth ) )``````
It does save recalculating 'startMonth' though.

#### GraH - Guido

##### Well-Known Member
Hi Guido
I wasn't convinced by the idea of comparing month numbers without taking the year into consideration.
True.

I like LET, but it's only for insiders, right? Only since last weekend I was able to upgrade my 365 subscription and finally XLOOKUP and the DA-functions show up. I need to revisit some of your previous posts to learn from them. ;-)

#### GraH - Guido

##### Well-Known Member
WHAT CHANGE I HAVE TO MADE IN FORMULA IF I DON'T WANT TO ADD ONE MONTH.(REFER NEW ATTACHMENT)
Why are you shouting?

I apologize for the typo in my formula, I think there is an inconsistency in your expected result column. And I overlooked it completely the first time.
Maybe this is what you ask now?
=EOMONTH(TEXT(A3,"0000\/00\/00"),IF(0+MID(A3,5,2)>MONTH(TODAY()),-13,-1))+1

#### Attachments

• 10 KB Views: 5

#### kv.singh

##### New Member
Thanks for your efforts. But I want formula for below mentioned conditions.(refer latest attachment)
 Expected result conditions. 01. first date of next month 02. if Month given D1 coloumn is greater than current month than year should be current year less one year(current year -1) 03 If month given in D1 is less than current month than year should be current year. Note : date given is D1 col. Is in text(yyyymmdd) format.

#### Attachments

• 9.7 KB Views: 5

#### Peter Bartholomew

##### Well-Known Member
I am beginning to lose track of this in terms of what the solution should be. Assuming the LET function is not available, I have reverted to Named formulae to hold intermediate calculations.

#### Attachments

• 10.7 KB Views: 2

#### Chihiro

##### Excel Ninja
Your expected result in B4 is wrong...

At any rate, you can try following.
=TEXT(DATE(YEAR(TODAY())-(MID(A3,5,2)+0>MONTH(TODAY())),MID(A3,5,2)+1,1),"yyyymmdd")

#### GraH - Guido

##### Well-Known Member
Okay, my last attempt:
=YEAR(TODAY())-((MID(A3,5,2)+0)>MONTH(TODAY()))&TEXT((MID(A3,5,2)+1),"00")&"01"

If it were my workbook, I would encode today's date in a cell to avoid the volatile nature of today. That is if there are many dates to convert.
Use like Peter the name manager to construct the bits of the formula only once.
=DATE(CurYr-(MthOfDate<=CurMth),NextMthOfDate,1)

#### Attachments

• 11 KB Views: 4

#### Chihiro

##### Excel Ninja
@GraH - Guido

Unless you use DATE function, When Mid(A3,5,2) = 12. It's going to give wrong result.

By using DATE function... DATE(2019,13,1) is interpreted as 2020-01-01.

#### kv.singh

##### New Member
@Chihiro
SORRY, MY EXPECTED RESULT IN B4 WAS WRONG. YOUR FORMULA IS GIVING EXPECTED RESULT. ONE LAST QUESTION. KINDLY PROVIDE FORMULA
IN D3 COL. I WANT TO ADD 12 MONTHS TO THE DATE GIVEN IN COLOUMN D2 AND IT SHOULD BE LAST DATE OF THAT MONTH.
 D1 D2 D3(EXPECTED RESULT) 19720603​ 20200701 20210630​ 19860926​ 20191001 20200930​ 20020113​ 20200201 20201231​ 20201125​ 20191201 20201130​ 20010416​ 20200501 20200430​

#### kv.singh

##### New Member
@GraH - Guido:
YOUR LAST ATTEMPT FORMULA IS GIVING EXPECTED RESULT. ONE LAST QUESTION. KINDLY PROVIDE FORMULA
IN D3 COL. I WANT TO ADD 12 MONTHS TO THE DATE GIVEN IN COLOUMN D2 AND IT SHOULD BE LAST DATE OF THAT MONTH.
 D1 D2 D3(EXPECTED RESULT) 19720603 20200701 20210630 19860926 20191001 20200930 20020113 20200201 20201231 20201125 20191201 20201130 20010416 20200501 20200430

Report Edit Delete

#### GraH - Guido

##### Well-Known Member
@GraH - Guido

Unless you use DATE function, When Mid(A3,5,2) = 12. It's going to give wrong result.

By using DATE function... DATE(2019,13,1) is interpreted as 2020-01-01.
, I stand corrected indeed, did not think ahead here. Thanks @Chihiro for pointing it out.

#### kv.singh

##### New Member
can anyone provide help .If possible include helper column in date format to arrive at result shown in D2 AND D3 COLOUMN. any formula ???

#### GraH - Guido

##### Well-Known Member
Hi, You actually received all elements to solve this. Sometimes it is appreciated you do try on your own.
Since I was sloppy in previous replies, I take some pride in correcting them.

PS: Maybe it's a good idea you reread the forum rules.
- in #12 & #13 you were SHOUTING again.
- in #15 you don't show patience (I'm enjoying my holidays, so my time on the forum is very limited)

#### Attachments

• 11 KB Views: 5

#### kv.singh

##### New Member
@GraH - Guido

In #12 I have used words"SORRY, MY EXPECTED RESULT IN B4 WAS WRONG "(I am accepting my mistake)
In #13 I have used words "YOUR LAST ATTEMPT FORMULA IS GIVING EXPECTED RESULT "(Appreciating your efforts)
In # 15 I am asking for help used words "If possible "
If any statement hearten you than I am sorry for my words. Enjoy your holidays. and after completing your holidays if you wish, you can reply my question #13 Your last reply is solution of my first query.
Thanks. Enjoy your holidays. Be safe, Take care.

#### GraH - Guido

##### Well-Known Member
I may have sounded harsher then i intended. With "PS" I wanted to say, "Consider taking note of".
Honestly, I thought having solved both your questions. But I see I have uploaded the wrong version of the attachment. #HolidaysConcentration.
I do appreciate your follow up, btw and thank you for wishing me good holidays.

#### Attachments

• 11.3 KB Views: 4

#### kv.singh

##### New Member
Thanks once again for providing solution to my quarries.