Excel 2007 : Date cells not being identified as dates !

**Tuche** · 07-14-2008, 02:41 PM

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.

Jul 10 00:02:17 -NickServ- XXX is ZZTTRR
Jul 10 00:02:17 -NickServ-   Time registered: May 28 21:40:13 2008 UTC
Jul 10 00:02:17 -NickServ-   Last seen time: Jun 28 23:13:44 2008 UTC
Jul 10 00:02:17 -NickServ- Last quit message: Quit:
Jul 10 00:02:17 -NickServ- ZZZ is UUUTTTDDD
Jul 10 00:02:17 -NickServ-   Time registered: May 28 21:40:13 2008 UTC
Jul 10 00:02:17 -NickServ-   Last seen time: Jun 28 23:13:44 2008 UTC
Jul 10 00:02:17 -NickServ- Last quit message: Quit:

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

Jul 10 00:02:17 -NickServ- XXX is ZZTTRR
Jul 10 00:02:17 -NickServ-   Time registered: May 28 21:40:13 2008 UTC
Jul 10 00:02:17 -NickServ-   Last seen time: Jun 28 23:13:44 2008 UTC
Jul 10 00:02:17 -NickServ- ZZZ is UUUTTTDDD
Jul 10 00:02:17 -NickServ-   Time registered: May 29 21:40:13 2008 UTC
Jul 10 00:02:17 -NickServ-   Last seen time: Jun 20 23:13:44 2008 UTC

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:

Jul 14 17:58:54 -NickServ-	JJJJJ is New Now Know How
Jul 14 17:58:54 -NickServ-	JJJJJ is currently online.
Jul 14 17:58:55 -NickServ-	  Time registered: Apr 19 17:51:30 2008 UTC

So, i had to tell Excel that online=today

Anyway, i worked this out too . . .

**NBVC** · 07-14-2008, 02:51 PM

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** · 07-14-2008, 02:57 PM

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** · 07-14-2008, 03:02 PM

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** · 07-14-2008, 03:11 PM

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

gives me #VALUE!

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

**Tuche** · 07-14-2008, 03:18 PM

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

on xlsx B2,

=IF(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;) = CONCATENATE(A2;" is currently online.");REPLACE(REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-1;",");FIND(" UTC";REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-1;",");1);LEN(REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-1;","));"");REPLACE(REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");1)-1;",");FIND(" UTC";REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");1)-1;",");1);LEN(REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;);1)+1;"");1)-1;","));""))

on xlsx D2,

=IF(OFFSET('DO NOT MODIFY #1'!$B$2;(ROW()-2)*3;0;;) = CONCATENATE(A2;" is currently online.");TODAY();REPLACE(REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-1;",");FIND(" UTC";REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-1;",");1);LEN(REPLACE(REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-3;FIND(":";REPLACE(OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1;FIND(":";OFFSET('DO NOT MODIFY #1'!$B$3;(ROW()-2)*3;0;;);1)+1;"");1)-1;","));""))

**NBVC** · 07-14-2008, 03:19 PM

Originally Posted by Tuche

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

**jonesie** · 11-17-2009, 04:24 PM

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** · 11-17-2009, 04:29 PM

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.