Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
I have a spreadsheet with an Eligibility Date for Upgrades, which is 10
months after it was upgraded. I need to determine when the device was originally updated. To do this, I need to subtract 10 months from the Eligibility Date. I've used the below formula but receive the #VALUE error. =DATE(YEAR(Q3),MONTH(Q3)-6,DAY(Q3)) Is there another formula I need to use to retrieve 10 months before the eligibility date? This eligiblity date is a vlookup from another excel spreadsheet, does this factor in when having the specific formula? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
I need to subtract 10 months from the Eligibility Date
I've used the below formula but receive the #VALUE error. =DATE(YEAR(Q3),MONTH(Q3)-6,DAY(Q3)) If you want 10 months prior why are you subtracting 6? If that formula returns a #VALUE! error chances are the value in Q3 is not a true Excel date. Try testing cell Q3 to see if it is in fact a true Excel date. Enter this formula in some cell: =ISNUMBER(Q3) If Q3 contains a true Excel date that formula will return TRUE. -- Biff Microsoft Excel MVP "AHizon via OfficeKB.com" <u38169@uwe wrote in message news:95f0ed72adc10@uwe... I have a spreadsheet with an Eligibility Date for Upgrades, which is 10 months after it was upgraded. I need to determine when the device was originally updated. To do this, I need to subtract 10 months from the Eligibility Date. I've used the below formula but receive the #VALUE error. =DATE(YEAR(Q3),MONTH(Q3)-6,DAY(Q3)) Is there another formula I need to use to retrieve 10 months before the eligibility date? This eligiblity date is a vlookup from another excel spreadsheet, does this factor in when having the specific formula? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
Hi AHizon,
Aside from the issue suggested by Biff, you're liable to run into problems with your formula when the month in Q3 has more days in it that the calcluated month and the day in Q3 is greater than the # days in the calculated month. To deal with that, you'll probably need a formula like: =MIN(DATE(YEAR(Q3),MONTH(Q3)-10,DAY(Q3)),DATE(YEAR(Q3),MONTH(Q3)-9,0)) -- Cheers macropod [Microsoft MVP - Word] "AHizon via OfficeKB.com" <u38169@uwe wrote in message news:95f0ed72adc10@uwe... I have a spreadsheet with an Eligibility Date for Upgrades, which is 10 months after it was upgraded. I need to determine when the device was originally updated. To do this, I need to subtract 10 months from the Eligibility Date. I've used the below formula but receive the #VALUE error. =DATE(YEAR(Q3),MONTH(Q3)-6,DAY(Q3)) Is there another formula I need to use to retrieve 10 months before the eligibility date? This eligiblity date is a vlookup from another excel spreadsheet, does this factor in when having the specific formula? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
On Tue, 12 May 2009 00:00:20 GMT, "AHizon via OfficeKB.com" <u38169@uwe wrote:
I have a spreadsheet with an Eligibility Date for Upgrades, which is 10 months after it was upgraded. I need to determine when the device was originally updated. To do this, I need to subtract 10 months from the Eligibility Date. I've used the below formula but receive the #VALUE error. =DATE(YEAR(Q3),MONTH(Q3)-6,DAY(Q3)) Is there another formula I need to use to retrieve 10 months before the eligibility date? This eligiblity date is a vlookup from another excel spreadsheet, does this factor in when having the specific formula? The #VALUE error likely means that the value in Q3 is not a real date. See if =ISNUMBER(Q3) gives a TRUE result to check that. If you have Excel 2007, or an earlier version with the Analysis ToolPak installed, then: =EDATE(Q3,-10) If this gives a #NAME error, see HELP for the EDATE function for instructions on installing the ATP. Otherwise, you can use this formula: =MIN(DATE(YEAR(Q3),MONTH(Q3)-{10,9},DAY(Q3)*{1,0})) Simply substracting ten months may give unexpected results if the day of the month in Q3 does not exist in the earlier month. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
Thanks for the tip...It doesn't seem the cell comes tru for a real date. But
when you look at the cell, it looks like a real date but Excel doesn't recognize it. I even tried to cut & paste special "Value" and Format to Date in hopes for Excel to recognize it as a real date. Unfortunately, the IsNumber formula still results in FALSE. How can I change an entire column as a date data for Excel to recognize instead of manually typing the date? Ron Rosenfeld wrote: I have a spreadsheet with an Eligibility Date for Upgrades, which is 10 months after it was upgraded. I need to determine when the device was [quoted text clipped - 5 lines] eligibility date? This eligiblity date is a vlookup from another excel spreadsheet, does this factor in when having the specific formula? The #VALUE error likely means that the value in Q3 is not a real date. See if =ISNUMBER(Q3) gives a TRUE result to check that. If you have Excel 2007, or an earlier version with the Analysis ToolPak installed, then: =EDATE(Q3,-10) If this gives a #NAME error, see HELP for the EDATE function for instructions on installing the ATP. Otherwise, you can use this formula: =MIN(DATE(YEAR(Q3),MONTH(Q3)-{10,9},DAY(Q3)*{1,0})) Simply substracting ten months may give unexpected results if the day of the month in Q3 does not exist in the earlier month. --ron -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
On Wed, 13 May 2009 15:20:02 GMT, "AHizon via OfficeKB.com" <u38169@uwe wrote:
Thanks for the tip...It doesn't seem the cell comes tru for a real date. But when you look at the cell, it looks like a real date but Excel doesn't recognize it. I even tried to cut & paste special "Value" and Format to Date in hopes for Excel to recognize it as a real date. Unfortunately, the IsNumber formula still results in FALSE. How can I change an entire column as a date data for Excel to recognize instead of manually typing the date? That is a little odd because Excel will often recognize date entries even if they are stored in a cell as text. So there may either be some incongruity in your system, or possibly some trailing or leading spaces or non-printing characters. In order to sort this out, do the following. Select a cell that contains a date and is giving you an error. Copy and paste the contents of the formula bar in your response: Then go to some blank area on your worksheet, (or another workbook or sheet) Enter the following formula in some cell: =CODE(MID($A$1,ROWS($1:1),1)) (for $A$1, substitute the cell reference for your error giving date cell; but BE SURE to use the ABSOLUTE reference style. In other words, if the cell is Q17, use $Q$17) Then fill down (copy/drag) until you start to get #VALUE! errors and post those results here also. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
Below is the content of the date data:
=VLOOKUP(L5, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\ [Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) When I use the =CODE(MID($Q$5,ROWS($1:1),1)) formula in a blank cell and get the #VALUE: cellQ16=VLOOKUP(L16, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) cellQ17=VLOOKUP(L17, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) cellQ18==VLOOKUP(L18, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) This continues on with the same formulat all the way down to the end of the column. Any assistance on how to get the data changed into a format that Excel will recognize as a date data would be greatly appreciated. Because as of current, it will only accept the data if I manually overwrite the date. Ron Rosenfeld wrote: Thanks for the tip...It doesn't seem the cell comes tru for a real date. But when you look at the cell, it looks like a real date but Excel doesn't recognize it. I even tried to cut & paste special "Value" and Format to Date in hopes for Excel to recognize it as a real date. Unfortunately, the IsNumber formula still results in FALSE. How can I change an entire column as a date data for Excel to recognize instead of manually typing the date? That is a little odd because Excel will often recognize date entries even if they are stored in a cell as text. So there may either be some incongruity in your system, or possibly some trailing or leading spaces or non-printing characters. In order to sort this out, do the following. Select a cell that contains a date and is giving you an error. Copy and paste the contents of the formula bar in your response: Then go to some blank area on your worksheet, (or another workbook or sheet) Enter the following formula in some cell: =CODE(MID($A$1,ROWS($1:1),1)) (for $A$1, substitute the cell reference for your error giving date cell; but BE SURE to use the ABSOLUTE reference style. In other words, if the cell is Q17, use $Q$17) Then fill down (copy/drag) until you start to get #VALUE! errors and post those results here also. --ron -- Message posted via http://www.officekb.com |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
On Wed, 13 May 2009 23:35:19 GMT, "AHizon via OfficeKB.com" <u38169@uwe wrote:
Below is the content of the date data: =VLOOKUP(L5, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\ [Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) Is this formula above in Q5? If so, do this precisely: place the cursor in the formula bar (the bar that starts with Fx) F9 (should display only the formula result, highlighted) <ctrl-c <esc (should return the formula to the formula bar. Place your cursor in the response window to this question. <ctrl-v (should paste the result of that formula here). If that formula is not in Q5, then I don't understand why you referred to Q5 in the formula =CODE(... below. When I use the =CODE(MID($Q$5,ROWS($1:1),1)) formula in a blank cell and get the #VALUE: If your date is showing in Q5, a #VALUE! error for this formula does not make any sense. How does these formulas below relate to the process I asked you to do? cellQ16=VLOOKUP(L16, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) cellQ17=VLOOKUP(L17, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) cellQ18==VLOOKUP(L18, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
Below is the result I get when I do an F9 on Q5.
#REF! I copied the below formula from the cells that have date-looking data that result in #VALUE using formula=CODE(MID($Q$5,ROWS($1:1),1)) when I drag the formula all the way down the column. I thought you wanted me to copy the formulas from those cells that result in #VALUE using formula=CODE(MID($Q$5, ROWS($1:1),1)) in a blank cell. cellQ16=VLOOKUP(L16, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, [quoted text clipped - 5 lines] REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) Ron Rosenfeld wrote: Below is the content of the date data: =VLOOKUP(L5, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\ [Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) Is this formula above in Q5? If so, do this precisely: place the cursor in the formula bar (the bar that starts with Fx) F9 (should display only the formula result, highlighted) <ctrl-c <esc (should return the formula to the formula bar. Place your cursor in the response window to this question. <ctrl-v (should paste the result of that formula here). If that formula is not in Q5, then I don't understand why you referred to Q5 in the formula =CODE(... below. When I use the =CODE(MID($Q$5,ROWS($1:1),1)) formula in a blank cell and get the #VALUE: If your date is showing in Q5, a #VALUE! error for this formula does not make any sense. How does these formulas below relate to the process I asked you to do? cellQ16=VLOOKUP(L16, 'M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, [quoted text clipped - 5 lines] REPORTS\[Eligibilty Upgrade Device Report.xls]deviceRpt-1'!$A$1:$J$300, 5, FALSE) --ron -- Message posted via http://www.officekb.com |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
On Thu, 14 May 2009 22:52:09 GMT, "AHizon via OfficeKB.com" <u38169@uwe wrote:
Below is the result I get when I do an F9 on Q5. #REF! Try that again with the workbook referenced being open. In other words, Open: M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\ [Eligibilty Upgrade Device Report.xls] and then put the cursor in the Fx bar and do the F9 I copied the below formula from the cells that have date-looking data that result in #VALUE using formula=CODE(MID($Q$5,ROWS($1:1),1)) when I drag the formula all the way down the column. I thought you wanted me to copy the formulas from those cells that result in #VALUE using formula=CODE(MID($Q$5, ROWS($1:1),1)) in a blank cell. No, I'm interested in the results of the formula itself. It will return the ASCII code for each character in Q5. When you start to get the VALUE errors, that's where there are no more characters left to process. You should see a series of two digit numbers before you see the VALUE errors. I'm interested in all of those numbers. --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
Should I send you the actual file to make it easier to understand? Because
even with me opening the actual file that it's referencing to obtain the Eligibility Date, it still gives me the #VALUE error. When I do the F9 on the cells that give me the #VALUE, below are some of the results: 05/09/2008 04/12/2008 08/11/2010 03/08/2007 01/21/2008 06/23/2008 02/22/2009 04/09/2009 01/18/2009 07/17/2008 06/18/2009 06/28/2009 06/28/2009 06/23/2010 08/24/2010 07/19/2008 09/05/2008 08/01/2008 It might be easier if you see the actual file I'm trying to work on...Is there a way to attach the file in this blog? Ron Rosenfeld wrote: Below is the result I get when I do an F9 on Q5. #REF! Try that again with the workbook referenced being open. In other words, Open: M:\Administrative Analyst\VERIZON\VRZN WRLSS\USAGE REPORTS\ [Eligibilty Upgrade Device Report.xls] and then put the cursor in the Fx bar and do the F9 I copied the below formula from the cells that have date-looking data that result in #VALUE using formula=CODE(MID($Q$5,ROWS($1:1),1)) when I drag the formula all the way down the column. I thought you wanted me to copy the formulas from those cells that result in #VALUE using formula=CODE(MID($Q$5, ROWS($1:1),1)) in a blank cell. No, I'm interested in the results of the formula itself. It will return the ASCII code for each character in Q5. When you start to get the VALUE errors, that's where there are no more characters left to process. You should see a series of two digit numbers before you see the VALUE errors. I'm interested in all of those numbers. --ron -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtract 10 months from a Speficic Date
On Fri, 15 May 2009 00:29:44 GMT, "AHizon via OfficeKB.com" <u38169@uwe wrote:
Should I send you the actual file to make it easier to understand? Because even with me opening the actual file that it's referencing to obtain the Eligibility Date, it still gives me the #VALUE error. The opening of the actual file was to avoid getting the #REF! error when you did the F9 bit. And that seems to have worked. Now you are getting what looks like dates rather than #REF errors. When I do the F9 on the cells that give me the #VALUE, below are some of the results: 05/09/2008 04/12/2008 08/11/2010 03/08/2007 01/21/2008 06/23/2008 02/22/2009 04/09/2009 01/18/2009 07/17/2008 06/18/2009 06/28/2009 06/28/2009 06/23/2010 08/24/2010 07/19/2008 09/05/2008 08/01/2008 Now what about the results of that formula that I posted to see what values come out. You know, the =CODE(MID($Q$5,ROWS($1:1),1)) where Q5 is one of the cells above. Enter that formula in some cell, then fill (copy/drag) down and report the results down to the #VALUE! error. You might see something like: 51 57 53 55 55 #VALUE! #VALUE! or you might see something like 53 47 57 47 50 48 48 56 #VALUE! #VALUE! or maybe even something like: 53 47 57 47 50 48 48 56 160 #VALUE! It might be easier if you see the actual file I'm trying to work on...Is there a way to attach the file in this blog? This is not a blog. Rather it is a newsgroup hosted on Microsoft servers. People access this newsgroup in a variety of ways. How to attach a file will depend on how you are accessing it, and that is something you'd have to figure out. I can't help you there. But most people are hesitant to download files from NG's because of all the abuse that is going on in the Internet. And you'd have to post not only the file giving the errors, but also the precedent files; and since your formulas won't refer to the same data source, you'd have to change them so they do, and it would be a real pain. In other words, I don't have an M: drive on my system, for one thing. Better to go through the steps and try to understand what's going on, I think. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Subtract 6 months off a date in excel | Excel Discussion (Misc queries) | |||
How do I subtract/add a known number of days/months from a date? | Excel Worksheet Functions | |||
subtract years and months (as in ages) from another age | Excel Discussion (Misc queries) | |||
Subtract 6 months | Excel Worksheet Functions | |||
Subtract MM/DD/YYYY and get # months | Excel Discussion (Misc queries) |