Cell E3 contains a date like this: 6/5/06
Cell E4 is supposed to show how many days have passed since that
date... so here's my formula:
=TODAY()-E3

The result is 1/20/1900

What am I doing wrong exactly?

I'm going to take a bunch of entries and average out the number of days
that have passed from each... but I can't seem to get an integer... i
just get an obscure date.

It's because Excel is formatting the result as a date.
Set the format to General and you will see the correct
value.

Andrew

Happybattles wrote:
Cell E3 contains a date like this: 6/5/06
Cell E4 is supposed to show how many days have passed since that
date... so here's my formula:
=TODAY()-E3

The result is 1/20/1900

What am I doing wrong exactly?

I'm going to take a bunch of entries and average out the number of days
that have passed from each... but I can't seem to get an integer... i
just get an obscure date.

Ok, finally got a number... but the number is 30.
Tried changing date to 5/5/2006 and it came up to 61...
Checked system time, its good.
The value should be zero... what else am i doing wrong?


Andrew Taylor wrote:
It's because Excel is formatting the result as a date.
Set the format to General and you will see the correct
value.

Andrew

Happybattles wrote:
Cell E3 contains a date like this: 6/5/06
Cell E4 is supposed to show how many days have passed since that
date... so here's my formula:
=TODAY()-E3

The result is 1/20/1900

What am I doing wrong exactly?

I'm going to take a bunch of entries and average out the number of days
that have passed from each... but I can't seem to get an integer... i
just get an obscure date.

Check your formatting or Regional settings - 6/5/2006 could
mean either 6 May or 5 June. The answers you get are
all correct under suitable interpretations of the date.



Happybattles wrote:
Ok, finally got a number... but the number is 30.
Tried changing date to 5/5/2006 and it came up to 61...
Checked system time, its good.
The value should be zero... what else am i doing wrong?


Andrew Taylor wrote:
It's because Excel is formatting the result as a date.
Set the format to General and you will see the correct
value.

Andrew

Happybattles wrote:
Cell E3 contains a date like this: 6/5/06
Cell E4 is supposed to show how many days have passed since that
date... so here's my formula:
=TODAY()-E3

The result is 1/20/1900

What am I doing wrong exactly?

I'm going to take a bunch of entries and average out the number of days
that have passed from each... but I can't seem to get an integer... i
just get an obscure date.

Doh! This is _July_! Works fine.
<smashes head on desk
Andrew Taylor wrote:
It's because Excel is formatting the result as a date.
Set the format to General and you will see the correct
value.

Andrew

Happybattles wrote:
Cell E3 contains a date like this: 6/5/06
Cell E4 is supposed to show how many days have passed since that
date... so here's my formula:
=TODAY()-E3

The result is 1/20/1900

What am I doing wrong exactly?

I'm going to take a bunch of entries and average out the number of days
that have passed from each... but I can't seem to get an integer... i
just get an obscure date.

Now I'm to the point where I need to get the average of all the
values... but those values which have not been listed are showing
31,000 days.

So, I did this to hide errors:

=IF(AVERAGE(E3:E29)180,"",AVERAGE(E3:E29))

But what I really want to do is average out the numbers which are
there, and ignore numbers greater than 180. I have three values I'm
averaging right now:
0
51
205

And its showing my average as being: <blank because its still
averaging the 31k days of the empty cells.

Any way to do this? Should I have the empty cells load a zero if the
days are greater than 180? Wouldn't that mess-up my real average?
Ugh... totally lost.

Tried:
=IF(AVERAGE(E3:E29)180,,AVERAGE(E3:E29))
and
=IF(AVERAGE(E3:E29)180,NULL,AVERAGE(E3:E29))
and
=IF(AVERAGE(E3:E29)180,NILL,AVERAGE(E3:E29))

Been a while since I've done any VB... but there must be a way to tell
it that there's no value whatsoever and to ignore it...

=AVERAGE(IF(E3:E26180,E3:E26))

This needs to be entered as an array formula:
press Ctrl-Alt-Enter instead of Enter after
entering the formula/



Happybattles wrote:
Tried:
=IF(AVERAGE(E3:E29)180,,AVERAGE(E3:E29))
and
=IF(AVERAGE(E3:E29)180,NULL,AVERAGE(E3:E29))
and
=IF(AVERAGE(E3:E29)180,NILL,AVERAGE(E3:E29))

Been a while since I've done any VB... but there must be a way to tell
it that there's no value whatsoever and to ignore it...

I'd change the original formula to this

=IF(E3="","",TODAY()-E3)

then when E3 is empty the result of this is a blank.

you can then just use a regular average on the range of results, blanks
will be ignored


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=558536