Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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. "Pete_UK" wrote: 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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 "904allen" wrote in message ... 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. "Pete_UK" wrote: 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 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 On Mar 29, 12:11*am, 904allen wrote: 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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 "Pete_UK" wrote: 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 "904allen" wrote in message ... 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. "Pete_UK" wrote: 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 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 On Mar 29, 5:33*pm, 904allen wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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. "904allen" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 On Mar 29, 8:31*pm, 904allen wrote: 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. "904allen" wrote: 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 - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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" wrote: 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 On Mar 29, 5:33 pm, 904allen wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
date range
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 On Mar 30, 2:47*am, 904allen wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Need true or false if a date falls between a date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |