Excel 2007 : Date cells not being identified as dates !

[Tuche]

Greetings,

after spending many hours and days trying to fix this problem, i could not solve it and i am here asking for your help

I am looking for a way to control the inactivity of people in my irc channel/server.

So, what i do is:
1- in irc i do a "/msg nickserv info XXX" the output information would look like this.

Please Login or Register  to view this content.

Good, with replace, i ge rid of useless info, like the last message rolls.

Please Login or Register  to view this content.

So, now i created a formula to:
*1* print in a specific column (Name), the nick (XXX and ZZZ). (I am not interested in the is ZZTTRR and is UUUTTTDDD)
*2* print the date registered in another column (Registered)
*3* TODAY() - "the printed registered date" in another column (Age)
*4* print the last seen date to another column (Last seen)
*5* TODAY() - "the printed last seen date" in another column (Inactivity)


Everything seem to be working really good.

The problem is that in the dates printed on *2* and *4* are not being seen as dates, so the TODAY()- "the cells" (on *3* and *5*), wont work, because "TODAY()- Apr 12,2008" is not possible ?

what am i missing here . . . ?


Please, i believe the BIG formula is correct . . .

edit,

between, i forgot to mention that the output text is not always in the same "format".

Sometimes, this pops up:

Please Login or Register  to view this content.

So, i had to tell Excel that online=today

Anyway, i worked this out too . . .

[NBVC]

You can wrap the date in the DateValue() function which converts date string to actual date value...

e..g =TODAY()-DATEVALUE("Apr 12,2008")


you can replace the date string with a cell reference containing it or a formula that results in it...

[Tuche]

Thanks,

i just tried that and still not working.
=TODAY()-DATEVALUE(B2)

B2 = Apr 19, 2008

Hehe after a so complex formula i made, i can not belive this small thing with dates is holding me off

[NBVC]

How is not working? Do you get an error or a wrong value?

You sheet doesn't help as you have Value errors in column B and I don't see the formulas that got them...

try saving as Excel 2003 and then attaching...

[Tuche]

=TODAY()-DATEVALUE("Apr 19, 2008")

gives me #VALUE!

weird, why can you not see my formulas of B and D ?

[Tuche]

in xls (97) it breaks everything, too many levels of nesting . . .

on xlsx B2,

Please Login or Register  to view this content.

on xlsx D2,

Please Login or Register  to view this content.

[NBVC]

=TODAY()-DATEVALUE("Apr 19, 2008")

gives me #VALUE!

weird, why can you not see my formulas of B and D ?

I get 86 as a result of that formula...

I don't have XL2007 so my converter screws something up when trying to convert to 2003 and removes some of your formulas...

[Tuche]

Hum that is weird, what could be causing the problem with TODAY()-DATEVALUE(Apr 22, 2008) ?

Any option ?

[NBVC]

You have to put the date in quotations... or reference a cell that has the date in a string format instead of an actual date format...

[Tuche]

Yes, i did.

To test, i just created a new blank document.

In the first cell, i enter . . .

Please Login or Register  to view this content.

and get #VALUE!

What is going wrong here

[NBVC]

What happens if you use this instead?

=TODAY( )-"Apr 19, 2008"+0

[Tuche]

#VALUE !

something is really screwed here . . .

do you think a reinstall would fix it ?

[NBVC]

Try the formula first in a brand new workbook.... just to make sure it's not something in your workbook causing the issue...

Also.. do you get same error with this: =TODAY( )-"19 Apr, 2008"+0

[Tuche]

Yes, i did exactly that.

Closed everything, then opened Excel and started a blank new workbook . . .

[NBVC]

Try opening the attached and see what you get in cell A1.

[Tuche]

Thanks a lot for the support NBVC !

But i got a bit angry that the error is not on my side but something wrong with Excel . . .

LoL, just to think the time i lost on just this small date thing . . .
TODAY - B2


So, i uninstalled it, will do a small clean up here, restart and reinstall it . . .

Hopefully will fix it . . .

[Tuche]

Try opening the attached and see what you get in cell A1.

Damn, uninstalled, did a small clean up, restarted, reinstalled in a different driver and folder . . .

I open your file and i get #VALUE!

What is wrong with my Excel ?

Windows Vista x64
M.Excel 2007

It must be an option, right ?

[NBVC]

could it be the way your date is written? Perhaps it has to do with the way date/time is setup on your system?

e.g

Try:

=Today()-Datevalue("19 Apr, 2008")

and other possible versions...

[Tuche]

That last one worked . . . 86 !

Now i need to find a way to fix my dates in my file . . . *wonders*

[daddylonglegs]

What is wrong with my Excel ?

Probably nothing.

I also get a #VALUE error with NBVC's file. That's because US (or Canadian) regional settings recognise "mmm dd, yyyy" as a valid date format....but UK (and probably German) regional settings don't.

Can you change the date format? dd-mmm-yyyy is universal, i.e. "19-Apr-2008" or do that conversion within your formula, e.g.

=TODAY()-SUBSTITUTE(REPLACE(B2,1,FIND(" ",B2),""),", ","-"&LEFT(B2,3)&"-")

format as general

[Tuche]

Thanks daddylonglegs,

are you sure that formula is correct, my head hurts already . . .

[NBVC]

Sorry for leading you somewhat astray Tuche... I didn't think that the way the date was written within the DATEVALUE function mattered at first... I don't play with dates much, let alone change and play with date settings... I just took a guess at the end and I guess it was right....

[jonesie]

A student of mine is experiencing the same problem working with Excel 2007 on a laptop. Is there an Excel setting that might be causing this error?

In future I'll read ALL the replies before posting!!!

[NBVC]

Sorry...

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[trucker10]

Thanks daddylonglegs,

are you sure that formula is correct, my head hurts already . . .

Bitte werfen Sie einen Blick ( take a look )

[NBVC]

A little late trucker10... that thread ended in July 2008!

[trucker10]

A little late trucker10... that thread ended in July 2008!

oops sorry, I had not seen the date