Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
I create this formula and copied it down.
=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Format as mm/dd/yy and you'll see what's going wrong
You'll have to think about what you mean by "same day" for a month where that day doesn't exist. Once you know, post again. There is always a solution. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dermot" wrote in message ... |I create this formula and copied it down. | | =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) | | I have custom formatted the date: mm/yy | | It works fine for most month sequences but when I enter an end of month date | like 31/01/2005 and copy it down....February is missing... | | Mar-05 | Apr-05 | May-05 | | Can any one explain what I am overlooking? | | Thanks in advance | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
You are taking the same day as in your original date, and if this is
31 then there is no date 31st February 2005 - in this case, Excel will wrap the number around and make it 3rd March 2005, and thereafter it will be the 3rd of each month - adjust your formatting to dd/mm/yy to see. You might like to change your formula to: =DATE(YEAR(B5), MONTH(B5)+1, 1) so you will always get the 1st day of the next month. Hope this helps. Pete On Nov 21, 7:28 pm, Dermot wrote: I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Hi Pete
Thanks for your explanation. Please clarify my understanding....by formatting mm/yy.....excel assume all months have the same number of days....hence when the a month doesn't have a particular day .......that month is skipped. Further Questions 1. In your formula: =DATE(YEAR(B5), MONTH(B5)+1, 1) What does the , 1 after +1 2. What formula could be used to generate the sequence from any day in a calendar month. IE. 31/1/2005 to 28/2/2005 to take into account that months have different numbers of days? Thanks in advance "Pete_UK" wrote: You are taking the same day as in your original date, and if this is 31 then there is no date 31st February 2005 - in this case, Excel will wrap the number around and make it 3rd March 2005, and thereafter it will be the 3rd of each month - adjust your formatting to dd/mm/yy to see. You might like to change your formula to: =DATE(YEAR(B5), MONTH(B5)+1, 1) so you will always get the 1st day of the next month. Hope this helps. Pete On Nov 21, 7:28 pm, Dermot wrote: I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Dermot,
If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Dermot,
check out my response to your original post for info that might answer these questions. HTH, Conan Kelly "Dermot" wrote in message ... Hi Pete Thanks for your explanation. Please clarify my understanding....by formatting mm/yy.....excel assume all months have the same number of days....hence when the a month doesn't have a particular day .......that month is skipped. Further Questions 1. In your formula: =DATE(YEAR(B5), MONTH(B5)+1, 1) What does the , 1 after +1 2. What formula could be used to generate the sequence from any day in a calendar month. IE. 31/1/2005 to 28/2/2005 to take into account that months have different numbers of days? Thanks in advance "Pete_UK" wrote: You are taking the same day as in your original date, and if this is 31 then there is no date 31st February 2005 - in this case, Excel will wrap the number around and make it 3rd March 2005, and thereafter it will be the 3rd of each month - adjust your formatting to dd/mm/yy to see. You might like to change your formula to: =DATE(YEAR(B5), MONTH(B5)+1, 1) so you will always get the 1st day of the next month. Hope this helps. Pete On Nov 21, 7:28 pm, Dermot wrote: I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Hi Conan
Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Dermot,
I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) You *should* be able to do that. If you have your cells formatted as "mm/yy", then if you enter "31/10/05", then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then entering "31/10/05" SHOULD display "Oct 05". Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Also, the arguments for DATE() are Year, Month, and Day (in that order): =DATE([Year],[Month],[Day]) So: =DATE(2007,11,21) will return 21/11/2007 =DATE(2005,10,31) will return 31/10/2005 =DATE(2006,5,1) will return 1/5/2006 Also, each argument can be negative, positive, calculated, greater than/less than expected range, etc... HTH, Conan "Dermot" wrote in message ... Hi Conan Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Thanks for the reply Conan
It was when I copied 31/01/2005 date down an skipped February Jan 05, Mar05 etc.... To Quote You Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Question So if the value reflects the day.......why did it skip the month of February when copied down. Please advise a little further.. Thanks in advance "Conan Kelly" wrote: Dermot, I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) You *should* be able to do that. If you have your cells formatted as "mm/yy", then if you enter "31/10/05", then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then entering "31/10/05" SHOULD display "Oct 05". Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Also, the arguments for DATE() are Year, Month, and Day (in that order): =DATE([Year],[Month],[Day]) So: =DATE(2007,11,21) will return 21/11/2007 =DATE(2005,10,31) will return 31/10/2005 =DATE(2006,5,1) will return 1/5/2006 Also, each argument can be negative, positive, calculated, greater than/less than expected range, etc... HTH, Conan "Dermot" wrote in message ... Hi Conan Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
I explained that earlier - if you start with 31/01/05 and increment
the month, there is no date of 31st February. What Excel does is to wrap this around and make it 03/03/05 (i.e. 3 more days after 28th Feb), which only displays as Mar 05. All subsequent increments of the month will be correct, as it will take them as being the 3rd of the month. Thus, February is not shown. Hope this helps. Pete On Nov 21, 10:09 pm, Dermot wrote: Thanks for the reply Conan It was when I copied 31/01/2005 date down an skipped February Jan 05, Mar05 etc.... To Quote You Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Question So if the value reflects the day.......why did it skip the month of February when copied down. Please advise a little further.. Thanks in advance "Conan Kelly" wrote: Dermot, I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) You *should* be able to do that. If you have your cells formatted as "mm/yy", then if you enter "31/10/05", then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then entering "31/10/05" SHOULD display "Oct 05". Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Also, the arguments for DATE() are Year, Month, and Day (in that order): =DATE([Year],[Month],[Day]) So: =DATE(2007,11,21) will return 21/11/2007 =DATE(2005,10,31) will return 31/10/2005 =DATE(2006,5,1) will return 1/5/2006 Also, each argument can be negative, positive, calculated, greater than/less than expected range, etc... HTH, Conan "Dermot" wrote in message ... Hi Conan Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Dermot,
Pete_UK is correct. Format your cells as "dd/mm/yyyy" so you can see the actual date that XL is calculating. using your example, running the formula "=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))" on the date 31/01/2005, this is going to be XL's calculation process: =DATE(YEAR(31/01/2005),MONTH(31/01/2005) + 1,DAY(31/01/2005)) =DATE(2005, 1 + 1, 31) =DATE(2005, 2, 31) (But there is not 31 days in Feb. XL will do the following.) =DATE(2005, 2, 28 + 3) (31 days supplied to the formula - 28 days in Feb = 3 days) =DATE(2005, 2 + 1, 3) (the 28 days in Feb was converted to 1 month and added to the month that was supplied to the formula) =DATE(2005, 3, 3) which returns "03/03/2005" Now running the same formula on "03/03/2005" will return "03/04/2005" your formula is adding 1 to the month, but is leaving the day the same, so: --31/01/2005 tries to become 31/02/2005 --but there is not 31 days in Feb, so 31/02/2005 will become 03/03/2005 (There is not 31 days in February. But if there were, February 31st would be 3 days after February 28th. Since there are only 28 days in Feb, February 31st is 3 days after February 28th. 3 days after Feb 28th is Mar 3rd) HTH. Please write back if you have any more questions or if my explanation just confuses you more. Also, look up the DATE() function in XL's help. It will explain the function, its syntax, arguments and give examples. Conan "Dermot" wrote in message ... Thanks for the reply Conan It was when I copied 31/01/2005 date down an skipped February Jan 05, Mar05 etc.... To Quote You Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Question So if the value reflects the day.......why did it skip the month of February when copied down. Please advise a little further.. Thanks in advance "Conan Kelly" wrote: Dermot, I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) You *should* be able to do that. If you have your cells formatted as "mm/yy", then if you enter "31/10/05", then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then entering "31/10/05" SHOULD display "Oct 05". Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Also, the arguments for DATE() are Year, Month, and Day (in that order): =DATE([Year],[Month],[Day]) So: =DATE(2007,11,21) will return 21/11/2007 =DATE(2005,10,31) will return 31/10/2005 =DATE(2006,5,1) will return 1/5/2006 Also, each argument can be negative, positive, calculated, greater than/less than expected range, etc... HTH, Conan "Dermot" wrote in message ... Hi Conan Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Thanks again Pete for Posting
"Pete_UK" wrote: I explained that earlier - if you start with 31/01/05 and increment the month, there is no date of 31st February. What Excel does is to wrap this around and make it 03/03/05 (i.e. 3 more days after 28th Feb), which only displays as Mar 05. All subsequent increments of the month will be correct, as it will take them as being the 3rd of the month. Thus, February is not shown. Hope this helps. Pete On Nov 21, 10:09 pm, Dermot wrote: Thanks for the reply Conan It was when I copied 31/01/2005 date down an skipped February Jan 05, Mar05 etc.... To Quote You Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Question So if the value reflects the day.......why did it skip the month of February when copied down. Please advise a little further.. Thanks in advance "Conan Kelly" wrote: Dermot, I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) You *should* be able to do that. If you have your cells formatted as "mm/yy", then if you enter "31/10/05", then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then entering "31/10/05" SHOULD display "Oct 05". Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Also, the arguments for DATE() are Year, Month, and Day (in that order): =DATE([Year],[Month],[Day]) So: =DATE(2007,11,21) will return 21/11/2007 =DATE(2005,10,31) will return 31/10/2005 =DATE(2006,5,1) will return 1/5/2006 Also, each argument can be negative, positive, calculated, greater than/less than expected range, etc... HTH, Conan "Dermot" wrote in message ... Hi Conan Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Down A formula advice......
Hi Conan
Thanks again for the break down explanation of how Excel does this, what more could I ask for :) Much appreciate you taking the time and patience Cheers Dermot "Conan Kelly" wrote: Dermot, Pete_UK is correct. Format your cells as "dd/mm/yyyy" so you can see the actual date that XL is calculating. using your example, running the formula "=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))" on the date 31/01/2005, this is going to be XL's calculation process: =DATE(YEAR(31/01/2005),MONTH(31/01/2005) + 1,DAY(31/01/2005)) =DATE(2005, 1 + 1, 31) =DATE(2005, 2, 31) (But there is not 31 days in Feb. XL will do the following.) =DATE(2005, 2, 28 + 3) (31 days supplied to the formula - 28 days in Feb = 3 days) =DATE(2005, 2 + 1, 3) (the 28 days in Feb was converted to 1 month and added to the month that was supplied to the formula) =DATE(2005, 3, 3) which returns "03/03/2005" Now running the same formula on "03/03/2005" will return "03/04/2005" your formula is adding 1 to the month, but is leaving the day the same, so: --31/01/2005 tries to become 31/02/2005 --but there is not 31 days in Feb, so 31/02/2005 will become 03/03/2005 (There is not 31 days in February. But if there were, February 31st would be 3 days after February 28th. Since there are only 28 days in Feb, February 31st is 3 days after February 28th. 3 days after Feb 28th is Mar 3rd) HTH. Please write back if you have any more questions or if my explanation just confuses you more. Also, look up the DATE() function in XL's help. It will explain the function, its syntax, arguments and give examples. Conan "Dermot" wrote in message ... Thanks for the reply Conan It was when I copied 31/01/2005 date down an skipped February Jan 05, Mar05 etc.... To Quote You Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Question So if the value reflects the day.......why did it skip the month of February when copied down. Please advise a little further.. Thanks in advance "Conan Kelly" wrote: Dermot, I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) You *should* be able to do that. If you have your cells formatted as "mm/yy", then if you enter "31/10/05", then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then entering "31/10/05" SHOULD display "Oct 05". Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of the cell is still going to be "31/10/05", and "31/10/05" is the value that will be used in any calculations that refer to this cell. Also, the arguments for DATE() are Year, Month, and Day (in that order): =DATE([Year],[Month],[Day]) So: =DATE(2007,11,21) will return 21/11/2007 =DATE(2005,10,31) will return 31/10/2005 =DATE(2006,5,1) will return 1/5/2006 Also, each argument can be negative, positive, calculated, greater than/less than expected range, etc... HTH, Conan "Dermot" wrote in message ... Hi Conan Thanks for the great explanation and examples. We must have cross posted as I missed your reply I have read quite a bit about dates ...it's like opening can of worms. I didn' t realise my error until today when I entered 31st of the month...I assume that although I custom formatted the date as mm/yy I thought I could still enter a full date dd/mm/yy and get for example Oct 05...I was wrong! :) I will investiage your examples thanks very much for posting. "Conan Kelly" wrote: Dermot, If you ALWAYS want the last day of the next month, use this formula: =DATE(YEAR(B5), MONTH(B5)+2,0) I use this formula very often. Also, lookup the DATE() function in help for more info. The 3 arguments (year, month, & day) can be negative or positive, and they can be above & beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for days). For example: (all dates in USA date format--m/d/yyyy) =DATE(2007,0,1) would return 12/1/2006 =DATE(2007,-1,1) would return 11/1/2006 =DATE(2007,13,1) would return 1/1/2008 =DATE(2007,14,1) would return 2/1/2008 =DATE(2007,1,95) would return 4/5/2007 HTH, Conan Kelly "Dermot" wrote in message ... I create this formula and copied it down. =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5)) I have custom formatted the date: mm/yy It works fine for most month sequences but when I enter an end of month date like 31/01/2005 and copy it down....February is missing... Mar-05 Apr-05 May-05 Can any one explain what I am overlooking? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Advice | Excel Worksheet Functions | |||
Advice on Creating an Excel Formula or Macro - PLEASE HELP!!!! | Excel Worksheet Functions | |||
need formula advice | Excel Worksheet Functions | |||
Formula Advice Needed | Excel Discussion (Misc queries) | |||
Almost got it !! but need advice | Excel Worksheet Functions |