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

Excel Automatic Problem

gyan123

New Member
Sir,
I have one problem in excel

I have two Sheets in excel
1. Example
2. Months

Now the problem is In Example Sheet, i have created Two Column "A" and "B"
Column A have Start Date="01-01-2014"
Column B have Finish Date="01-12-2014"

I want excel auto consider Column A i.e. 01-01-2014 to Column B i.e. 01-12-2014, it will auto fill in Column"C" 01-01-2014, 01-02-2014, 01-03-2014..........01-12-2014 auto

I am unable to do this...........please help
 

Attachments

  • Auto.xlsx
    21.3 KB · Views: 0
HTML:
    A    B    C    D
1    Start    Finish    Output will be 
2    Col. A    Col. B    Col. C 
3    01/01/2014    31/12/2014    01.01.2014    01/01/2014
4              01.02.2014    01/02/2014
5              01.03.2014    01/03/2014
6              01.04.2014    01/04/2014
7              01.05.2014    01/05/2014
8              01.06.2014    01/06/2014
9              01.07.2014    01/07/2014
10              01.08.2014    01/08/2014
11              01.09.2014    01/09/2014
12              01.10.2014    01/10/2014
13              01.11.2014    01/11/2014
14              01.12.2014    01/12/2014

Spreadsheet Formulas
Cell    Formula
D3    =A3
D4    =EDATE(D3,1)
D5    =EDATE(D4,1)
Had to change your value in A3 to a real Excel date by replacing the dots with slashes.
Formula in D4 can be just copied down as far as necessary.
Haven't used the finish date - don't know how far you want the automation to go.
Also don't know if you want a macro to do this or pure worksheet formulae.
 
Last edited:
Hi gyan123, and welcome to the forum :awesome:

If you enter valid dates in your cells, you can use this:
=IF(EDATE($A$3,ROW(A1)-1)<$B$17,EDATE($A$3,ROW(A1)-1),"")
copy down as required...

and use this for the dates already in your sheet:
=IF(EDATE(DATE(RIGHT($A$3,4),MID($A$3,FIND(".",$A$3)+1,2)+ROWS(A$1:A1),LEFT($A$3,2)),-1)<DATE(RIGHT($B$3,4),MID($B$3,FIND(".",$B$3)+1,2),LEFT($B$3,2)),EDATE(DATE(RIGHT($A$3,4),MID($A$3,FIND(".",$A$3)+1,2)+ROWS(A$1:A1),LEFT($A$3,2)),-1),"")

Regards,
 
Hi,

You may use

=SUBSTITUTE(A3,".","/")

to convert to real dates then use edate as shown above posts.
 
Back
Top