Subtract 10 months from a Speficic Date

[AHizon via OfficeKB.com]

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?

 

[T. Valko]

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.

 

[macropod]

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))

 

[Ron Rosenfeld]

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

 

[AHizon via OfficeKB.com]

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?

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

 

[Ron Rosenfeld]

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

 

[AHizon via OfficeKB.com]

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]

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

 

[AHizon via OfficeKB.com]

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)

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

 

[Ron Rosenfeld]

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

 

[AHizon via OfficeKB.com]

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?

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

 

[Ron Rosenfeld]

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