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

[kv.singh]

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 .

 

[GraH - Guido]

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.

 

[kv.singh]

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

 

[Peter Bartholomew]

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!

= 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]

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]

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

 

[kv.singh]

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.

 

[Peter Bartholomew]

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.

 

[Chihiro]

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]

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)

 

[Chihiro]

@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]

@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]

@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

Reply
Report Edit Delete

 

[GraH - Guido]

@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]

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]

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)

 

[kv.singh]

@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]

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.

 

[kv.singh]

Thanks once again for providing solution to my quarries.