• 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.

Add one month to 1st of next month current date

kv.singh

New Member
I have date in text format as 20200612, 20201207 (yyyymmdd) I want to add 12 months to1st of next month date. The date should be 20200630 for 20200612
and for 20201227 date should be 20120131.
sample file is attached formula required to get data as shown in green colour.
 

Attachments

  • sample-2.xlsx
    8.3 KB · Views: 6
Hello
Your actual dates are numbers not text ( they are right aligned in the cell)
This being said, with the use of a helper column which you could hide and custom formatting of the cell the attached does what you want
 

Attachments

  • sample-2-1.xlsx
    9.8 KB · Views: 5
In col. A is my basic data. I want result as shown in col .B and C. Your formula gives result 23-01-20,21-04-20,06-03-20,09-12-20
month and year is OK I want date as last date of the month in col.C and first date of next month in col.B. you can take help of helper col.​
 
In B4:

=EOMONTH(DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2)),0)+1

In C4:

=EDATE(B4,12)

DATEREQUIREDREQUIRED
DATE -1DATE-2
1st of next monthplus 12 month to Date-1
20200123​
20200201​
20210201​
20200421​
20200501​
20210501​
20200306​
20200401​
20210401​
20201209​
20210101​
20220101​

Custom date format for B and C: yyyymmdd
 
Last edited:
Excellent. Formula is working fine. Is there any to formula to convert only year to current year in date for example :-
01. 19960123 convert to 20200123
02. 20180421 convert to 20200421
03. 19711209 convert to 20201209
I will be very thankful to you if you can provide formula as mentioned above.
 
EDIT: for the original question.
Or this might work too
in B4:= EOMONTH(TEXT(A4,"0000\/00\/00")+0,0)+1
in C4: =EDATE(B4;12)

And format cells as "yyyymmdd".
 

Attachments

  • chandoo44641_dateFromText.xlsx
    11 KB · Views: 4
Last edited:
One Last question. How can we combine formula =DATE(YEAR(TODAY()),MID(A4,5,2),RIGHT(A4,2)) and =DATE(YEAR(TODAY()),MID(A4,5,2),RIGHT(A4,2)) to get result as under:-
01. 19960123 convert to 20200201
02. 20180421 convert to 20200501
03. 19711209 convert to 20201201
04. 19911225 convert to 20210101
 
Excellent. Formula is working fine. Is there any to formula to convert only year to current year in date for example :-
Hi,
Depending on the number of data and formulas in your workbook, having TODAY() all over the place can be very bad for performance. Since you only need the year, you might consider using a hardcoded variable in the name manager as vCurYear = 2020.

On your new data, you can try:
B4:=EOMONTH((vCurYr&TEXT(RIGHT(A10,4),"\/00\/00"))+0,0)+1
 

Attachments

  • chandoo44641_dateFromText.xlsx
    11.2 KB · Views: 2
Back
Top