• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • sample-15.xlsx
    9.4 KB · Views: 6
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

  • Copy of sample-15.xlsx
    9.9 KB · Views: 9
WHAT CHANGE I HAVE TO MADE IN FORMULA IF I DON'T WANT TO ADD ONE MONTH.(REFER NEW ATTACHMENT)
 

Attachments

  • SAMPLE-16.xlsx
    9.6 KB · Views: 6
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.
 
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. ;-)
 
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

  • Copy of sample-16.xlsx
    10 KB · Views: 5
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

  • SAMPLE-17.xlsx
    9.7 KB · Views: 6
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.
70097
 

Attachments

  • SAMPLE-17.xlsx
    10.7 KB · Views: 2
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")
 
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

  • Copy of SAMPLE-17.xlsx
    11 KB · Views: 4
@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.
 
@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.
D1D2D3(EXPECTED RESULT)
19720603​
20200701
20210630​
19860926​
20191001
20200930​
20020113​
20200201
20201231​
20201125​
20191201
20201130​
20010416​
20200501
20200430​
 
@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.
D1D2D3(EXPECTED RESULT)
197206032020070120210630
198609262019100120200930
200201132020020120201231
202011252019120120201130
200104162020050120200430

Reply
Report Edit Delete
 
can anyone provide help .If possible include helper column in date format to arrive at result shown in D2 AND D3 COLOUMN. any formula ???
 
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

  • Copy of SAMPLE-17.xlsx
    11 KB · Views: 5
@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.
 
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. :oops: #HolidaysConcentration.
I do appreciate your follow up, btw and thank you for wishing me good holidays.
 

Attachments

  • SAMPLE-17b.xlsx
    11.3 KB · Views: 5
Back
Top