date range

[904allen]

Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08 or A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07 or A3=1/1/07 B3=12`/31/07
A4=12/31/06 B4=1/1/06 or A4=1/1/06 B3=12/31/06
A5=3/1/05 B5=12/31/05 or A5=1/1/05 B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can be
any date between 3 years. As you can see I have to have excel list each year
by date

 

[Pete_UK]

So, it's got to be in decreasing order? Did you see the solution I
gave you for increasing order?

Pete

 

[904allen]

Yes Pete and thanks but what i'm after is a way that all the user has to do
is enter the to and from date once. 3/1/05 to 3/1/08 it doesn' matter if its
increasing or decreasing, the formula you supply is a lot shorter then the
way i was originally doing it but but your formula and the old way i was
doing it reguires the user to enter in the dates for all the years I'm try to
aviod that if they just can enter the date in once.

So, it's got to be in decreasing order? Did you see the solution I
gave you for increasing order?

Pete

Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08 or A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07 or A3=1/1/07 B3=12`/31/07
A4=12/31/06 B4=1/1/06 or A4=1/1/06 B3=12/31/06
A5=3/1/05 B5=12/31/05 or A5=1/1/05 B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can be
any date between 3 years. As you can see I have to have excel list each year
by date

 

[Pete_UK]

Here's an extract of a post to you from a couple of days ago:

"...
Anyway, if you do (want to split by year) then put the start date in A1 and
the final date in
B1 (or get your Users to do so), and then put the following formulae
in the cells stated below:

A2: =IF(A1="","",A1)

B2: =IF(A2="","",IF(DATE(YEAR(A2),12,31)>B$1,B$1,DATE(YEAR(A2),
12,31)))

A3: =IF(OR(A2="",B2=B$1),"",DATE(YEAR(A2)+1,1,1))

Copy B2 into B3, and then copy A3:B3 down into as many cells as you
feel you need. All of the cells in columns A and B should be formatted
as a date in the format you prefer.

The other formulae I gave you will still work with this set up, so
they can be copied down the same number of rows as your date formulae.

Note that I have used the approach outlined in your first post, i.e.
listing the oldest dates first, rather than in your latest posting
which lists the most recent dates first.

So now your Users only have to enter the start date into A1 and the
final end date into B1 and the rest of it will automatically appear.

I hope this is finally what you want.

Pete
.... "

Presumably you did not get this, as it does what you ask for.

Hope this helps.

Pete

Yes Pete and thanks but what i'm after is a way that all the user has to
do
is enter the to and from date once. 3/1/05 to 3/1/08 it doesn' matter if
its
increasing or decreasing, the formula you supply is a lot shorter then the
way i was originally doing it but but your formula and the old way i was
doing it reguires the user to enter in the dates for all the years I'm try
to
aviod that if they just can enter the date in once.

So, it's got to be in decreasing order? Did you see the solution I
gave you for increasing order?

Pete

Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will
never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08 or A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07 or A3=1/1/07 B3=12`/31/07
A4=12/31/06 B4=1/1/06 or A4=1/1/06 B3=12/31/06
A5=3/1/05 B5=12/31/05 or A5=1/1/05 B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can
be
any date between 3 years. As you can see I have to have excel list each
year
by date

 

[904allen]

Pete thank you for your patience you must think I a nurd. but i'm trying. I
did what you suggested and i even played around with the formulas. I don't
know if this makes a difference but the user can put in any dates up to three
years.when i inserted your formulas the last date went to 12/31/what ever
year. if the user puts in 1/2/05 to 1/1/08 the last year should go tfrom
1/1/05 to 1/2/05 instead it goes to 12/31/05.

 

[Pete_UK]

Did you see this bit?:

"...
Copy B2 into B3, and then copy A3:B3 down into as many cells as you
feel you need. All of the cells in columns A and B should be formatted
as a date in the format you prefer.
..."

You need to copy the formulae down to cover the number of years you
might expect - you can copy down to row 10 if you wish, but you will
just get blanks on those rows beyond your finish date.

Don't forget that the start date should be in A1 and the finish date
in B1.

Hope this helps.

Pete

Pete thank you for your patience you must think I a nurd. but i'm trying. I
did what you suggested and i even played around with the formulas. I don't
know if this makes a difference but the user can put in any dates up to three
years.when i inserted your formulas the last date went to 12/31/what ever
year. if the user puts in 1/2/05 to 1/1/08 the last year should go tfrom
1/1/05 to 1/2/05 instead it goes to 12/31/05.

Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08              or     A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07           or    A3=1/1/07  B3=12`/31/07
A4=12/31/06 B4=1/1/06           or    A4=1/1/06  B3=12/31/06
A5=3/1/05 B5=12/31/05           or     A5=1/1/05  B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can be
any date between 3 years. As you can see I have to have excel list each year
by date- Hide quoted text -

- Show quoted text -

 

[904allen]

Pete i did exactly what you said and still can't get it to work it appears to
work if I put the dates in and then enter the formalus but the last date
still goes to the end of the year not to the last date. if the formulas are
enter pior to entering the dates it doesn't work correctly at all. Sorry Allen

Here's an extract of a post to you from a couple of days ago:

"...
Anyway, if you do (want to split by year) then put the start date in A1 and
the final date in
B1 (or get your Users to do so), and then put the following formulae
in the cells stated below:

A2: =IF(A1="","",A1)

B2: =IF(A2="","",IF(DATE(YEAR(A2),12,31)>B$1,B$1,DATE(YEAR(A2),
12,31)))

A3: =IF(OR(A2="",B2=B$1),"",DATE(YEAR(A2)+1,1,1))

Copy B2 into B3, and then copy A3:B3 down into as many cells as you
feel you need. All of the cells in columns A and B should be formatted
as a date in the format you prefer.

The other formulae I gave you will still work with this set up, so
they can be copied down the same number of rows as your date formulae.

Note that I have used the approach outlined in your first post, i.e.
listing the oldest dates first, rather than in your latest posting
which lists the most recent dates first.

So now your Users only have to enter the start date into A1 and the
final end date into B1 and the rest of it will automatically appear.

I hope this is finally what you want.

Pete
.... "

Presumably you did not get this, as it does what you ask for.

Hope this helps.

Pete

Yes Pete and thanks but what i'm after is a way that all the user has to
do
is enter the to and from date once. 3/1/05 to 3/1/08 it doesn' matter if
its
increasing or decreasing, the formula you supply is a lot shorter then the
way i was originally doing it but but your formula and the old way i was
doing it reguires the user to enter in the dates for all the years I'm try
to
aviod that if they just can enter the date in once.

So, it's got to be in decreasing order? Did you see the solution I
gave you for increasing order?

Pete

On Mar 28, 8:54 am, 904allen <[email protected]>
wrote:
Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will
never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08 or A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07 or A3=1/1/07 B3=12`/31/07
A4=12/31/06 B4=1/1/06 or A4=1/1/06 B3=12/31/06
A5=3/1/05 B5=12/31/05 or A5=1/1/05 B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can
be
any date between 3 years. As you can see I have to have excel list each
year
by date

 

[Pete_UK]

Okay Allen,

to demonstrate this working I started with a completely blank
worksheet and I entered these formulae into the cells stated:

A2: =IF(OR(A1="",B1=""),"",A1)
A3: =IF(OR(A2="",B2=B$1),"",DATE(YEAR(A2)+1,1,1))

I then copied A3 into the range A4:A6 by selecting A3, clicking
<copy>, moving cursor into A4, holding down the <SHIFT> key and
clicking on A6, releasing the <SHIFT> key and then pressing <ENTER>.
This gave me the following formulae in those cells:

A4: =IF(OR(A3="",B3=B$1),"",DATE(YEAR(A3)+1,1,1))
A5: =IF(OR(A4="",B4=B$1),"",DATE(YEAR(A4)+1,1,1))
A6: =IF(OR(A5="",B5=B$1),"",DATE(YEAR(A5)+1,1,1))

At this stage I could not see anything displayed in these cells - you
can only see the formulae in the formula bar.

I then put this formula in B2:

=IF(A2="","",IF(DATE(YEAR(A2),12,31)>B$1,B$1,DATE(YEAR(A2),12,31)))

and copied this down into the cells B3:B6 and the formula in B6 was:

=IF(A6="","",IF(DATE(YEAR(A6),12,31)>B$1,B$1,DATE(YEAR(A6),12,31)))

Again, I couldn't see anything at this stage. I then formatted the
cells A1:B6 as a date format.

I then put the date 1/02/2006 in cell A1 (i.e. 1st February 2006 - you
would probably enter it as 2/01/2006) and again nothing displayed in
the cells with the formulae in. Finally, I put this date in B1 -
1/03/2008 (probably 3/01/2008 for you) and immediately I saw this
result in A2 onwards:

01/02/2006 31/12/2006
01/01/2007 31/12/2007
01/01/2008 01/03/2008

Changing the date in A1 to 1/02/2004 gave me these results:

01/02/2004 31/12/2004
01/01/2005 31/12/2005
01/01/2006 31/12/2006
01/01/2007 31/12/2007
01/01/2008 01/03/2008

These are straight copies from the worksheet, though obviously you
wouldn't have such a long span from what you have said.

I also put these formulae in the cells stated:

D2: =IF(OR(A2="",B2=""),"",INT((B2-A2+1)/7))
E2: =IF(D2="","",MOD(B2-A2+1,7))

and copied these down to row 6, and they gave me the number of weeks
in column D and the days in column E for the dates in each row, i.e
for the second set of dates:

47 6
52 1
52 1
52 1
8 5

This is what you asked for - the User enters a start date in A1 and a
finish date in B1 (and these could span several years), and you will
get a break down of start date and end date for each year in the span
as well as the number of weeks and days in each year. Both dates have
to be entered, and the formulae treat these as inclusive dates.

I suggest that you check out your formulae again - better still, copy
them directly from this posting.

Hope this helps.

Pete

 

[904allen]

Pete, I did exactly what you suggested. the formulas worked if I put them in
ater I put in the dates except for the last date it still went to the end of
the year not the end of the date inserted. also if I put the forulas in first
it sets the in daye onthe cells at the end date in all cells. does it have
some thing to fo the progrem I'm using 2003 or do I need to set something
before running your formulas.

Pete thank you for your patience you must think I a nurd. but i'm trying. I
did what you suggested and i even played around with the formulas. I don't
know if this makes a difference but the user can put in any dates up to three
years.when i inserted your formulas the last date went to 12/31/what ever
year. if the user puts in 1/2/05 to 1/1/08 the last year should go tfrom
1/1/05 to 1/2/05 instead it goes to 12/31/05.

Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08 or A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07 or A3=1/1/07 B3=12`/31/07
A4=12/31/06 B4=1/1/06 or A4=1/1/06 B3=12/31/06
A5=3/1/05 B5=12/31/05 or A5=1/1/05 B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can be
any date between 3 years. As you can see I have to have excel list each year
by date

 

[Pete_UK]

I don't know why you are having problems. Perhaps you can copy/paste
the actual formulae that you are using, particularly the ones in
column B, to see if there is any difference. You have included the $
symbols where they appear in my formulae?

Alternatively, send me an email to:

pashurst <at> auditel.net

and I'll send you my working file back. If you like, you can attach
your own file.

Pete

Pete, I did exactly what you suggested. the formulas worked if I put them in
ater I put in the dates except for the last date it still went to the end of
the year not the end of the date inserted. also if I put the forulas in first
it sets the in daye onthe cells at the end date in all cells. does it have
some thing to fo the progrem I'm using 2003 or do I need to set something
before running your formulas.

Pete thank you for your patience you must think I a nurd. but i'm trying.. I
did what you suggested and i even played around with the formulas. I don't
know if this makes a difference but the user can put in any dates up to three
years.when i inserted your formulas the last date went to 12/31/what ever
year. if the user puts in 1/2/05 to 1/1/08 the last year should go tfrom
1/1/05 to 1/2/05 instead it goes to 12/31/05.

lues"904allen" wrote:

Dates Range need Help
Example
[A1 has date 3/1/08] [B1 has date 3/1/05} date between A1 and B1 will never
be more than 3 years. How do I get Excel list
A2=1/1/08 B2=3/1/08              or     A2=3/1/08 B2=1/1/08
A3=12/31/07 B2=1/1/07           or    A3=1/1/07  B3=12`/31/07
A4=12/31/06 B4=1/1/06           or    A4=1/1/06  B3=12/31/06
A5=3/1/05 B5=12/31/05           or     A5=1/1/05  B5=3/1/05
As stated the dates in A1 to B2 will never be more then 3 years but can be
any date between 3 years. As you can see I have to have excel list each year
by date- Hide quoted text -

- Show quoted text -

 

[904allen]

Hi Pete,
I went back and instead of typing in the formulas I cut and pasted as you
suggested and it works like a charm, Thanks for sticking with me I really
appreciate all the time and help you gave me. You probably think this was a
small thing but to me, You made my month.
Allen

 

[Pete_UK]

Good to hear it, Allen. I'm glad you finally got something working,
and hopefully it will save you and your users a lot of time in the
future.

Pete