Problem using named range in Offset

[JenniferMurphy]

I thought I understood this from some previous discussions here, but apparently I do not.

This table shows three different ways to calculate the number of days between two dates. Column C has been assigned the name "Date".

The first one (Col D), uses relative cell addresses. The second uses the named range for the first term, but a cell reference in the Offset call. Both of these work.

The third one, which uses the named range in the Offset call, gets an error.

R/C	C	D	E	F	G	H	I
5	Date	Days	Formula	Days	Formula	Days	Formula
6	4/06/20​	5	D6: =C6-C7	5	F6: =Date-OFFSET(C6,1,0)	#REF!​	H6: =Date-(OFFSET(Date,1,0))
7	4/01/20​	40	D7: =C7-C8	40	F7: =Date-OFFSET(C7,1,0)	#REF!​	H7: =Date-(OFFSET(Date,1,0))
8	2/21/20​	98	D8: =C8-C9	98	F8: =Date-OFFSET(C8,1,0)	#REF!​	H8: =Date-(OFFSET(Date,1,0))
9	11/15/19​	n/a	n/a	n/a	n/a		n/a

I tried several variations that I thought were suggested by various people here, but none of them work.

The expression

=Date-(OFFSET(+Date,1,0))

, gets an error.

My attempt to use intersect operator,

=Date-(row(H6)+1 Date)

, gets an error.

Is there a simple expression to subtract the date in the previous row from the one in the current row that uses only the named range (no cell references)?

 

[JenniferMurphy]

This is because when you enter 7:7 in a formula, Excel interprets this as a range (row #7).

If you construct "7:7" as =ROW()&":"&ROW(), its a text string, not row #7, and =range string does not work. For it to work, the string should be wrapped in INDIRECT.

OK, I think I get it. It's confusing because Excel seems to go out of its way to treat text that looks like numbers as numbers, but then in some situations, it gets finicky.

jasonb75's solution is working great, so I am happy, but thanks for the little tutorial.

 

[Tetra201]

You are welcome.