Error with Calculating date difference using TODAY()
 

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.

Error with Calculating date difference using TODAY()
 

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.

Error with Calculating date difference using TODAY()
 

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.

Error with Calculating date difference using TODAY()
 

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.

Error with Calculating date difference using TODAY()
 

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.

Error with Calculating date difference using TODAY()
 

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.

Error with Calculating date difference using TODAY()
 

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

Error with Calculating date difference using TODAY()
 

=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...

Error with Calculating date difference using TODAY()
 

Andrew Taylor wrote:
=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...

That part works now, thank you.
Now I'm trying to get the cell that tells the date difference to:
Load a null value into the cell if the number of days is greater than
360.

But it isn't working. The average is still showing:
38818

The loaded values of 29 a
0
51
23

Here's the formula I'm trying... but I still think its showing a huge
value for number of days when it has nothing to compare it to:
{=AVERAGE(IF(E3:E29180,E3:E29))}

This is essentially a nested IF-THEN statement... but it's loading off
the results of another IF-THEN... This is what is in the cell its
looking at:
=IF(TODAY()-E3<200,IF(TODAY()-E3360,"",TODAY()-E3),"")

Error with Calculating date difference using TODAY()
 

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