• 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'10 formula for a list of dates between two dates without repetition

6

6tel

Member
Hello.


I'm trying to figure out a formula to get a list of dates between two dates. I want these dates to list automatically in just one column when I insert two dates (start date and end date) in two separate cells that I have in another worksheet. Description of the situation as follows:


---------------------------------------------

Worksheet 1

(named "Portada". Contains two cells where the start date and end date actually are):


Cell D14 (Start date). Eg: 08/24/12

Cell D15 (End date). Eg: 08/31/12


---------------------------------------------

Worksheet 2:

(named "calcula". I need the formula to be placed at the column AZ and display dates like this:


Row │ Column AZ

8 │ 08/24/12

9 │ 08/25/12

10 │ 08/26/12

11 │ 08/27/12

12 │ 08/28/12

13 │ 08/29/12

14 │ 08/30/12

15 │ 08/31/12

16 │ -

17 │ -

18 │ -


---------------------------------------------

I'm using this formula for column AZ, but I don't want the formula to repeat dates when I drag it down for the whole column (AZ:AZ). I want it to stop listing dates right after coping the last date (end date) and write nothing after (in the example above where you see the dashes " - "):


=IF($AZ$8=Portada!$D$15," ",IF($AZ$8>Portada!$D$15," ",IF($AZ$8<Portada!$D$14," ",IF(AZ8<Portada!$D$15,AZ8+1,IF(AZ8>Portada!$D$14,AZ8+1,IF(AZ8=Portada!$D$15,Portada!$D$15," "))))))


Can you guys/gals help me? I've been stucked with this for hours and have tried everything. I need this to be a formula and to automatically work without changing the situation of the problem (no dragging, no advanced filter, no nothing else).
 
Hi, 6tel!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Type this in 2nd. worksheet at cell AZ8 and copy down as needed:

=SI(StartDate+FILA()-FILA(AZ$7)-1<=EndDate;StartDate+FILA()-FILA(AZ$7)-1;"-") -----> in english: =IF(StartDate+ROW()-ROW(AZ$7)-1<=EndDate,StartDate+ROW()-ROW(AZ$7)-1,"-")


Where StartDate and EndDate are two named ranges defined for 1st. worksheet's cells D14&D15 (not needed, just for easiness while writing).


Give a look at this file, if needed:

https://dl.dropbox.com/u/60558749/Excel%2710%20formula%20for%20a%20list%20of%20dates%20between%20two%20dates%20without%20repetition%20%28for%206tel%20at%20chandoo.org%29.xlsx


Regards!


PS: o Saludos!
 
Dios mío... I'm in love.


SirJB7, you truly are a ninja! A sorcerer, el brujo del Excel, bacanísimo, lo máximo (o como diríamos en Venezuela, "el chivo")...


I was really anxious. Don't know how to pay you this big favor you've done to me. In the meantime I'll return it to the others at the forum when being desperate as I was earlier before your answer.


De verdad, muchas, muchas gracias and lots of blessings to you.


---------


For the others not familiar with named ranges, here's an equivalent of SirJB7's formula to my example above without named ranges:


=IF(Portada!$D$14+ROW()-ROW(AZ$7)-1<=Portada!$D$15,Portada!$D$14+ROW()-ROW(AZ$7)-1,"-")
 
Hi, 6tel!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: please download again the updated file from same link... just in case...
 
You must log in or register to reply here.
Back
Top