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

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

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

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

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

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

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

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