ExcelTip.com
Account Icon Account Icon Account Icon
ExcelTip.com

Go Back   Excel Help Forum > Usenet Groups > Excel 2007 Help
Date cells not being identified as dates ! Date cells not being identified as dates !
Register FAQ Forum Rules Members List Calendar Search Today's Posts Mark Forums Read

Notices
Hi All,
The big green guy here, just wanted to let you all know that we now have the reputation system in place, the reputation system allows you to give a visible pat on the back to those that have helped you or whose information has been helpful.

You will see an icon in every post (Light Blue scales), click this to give the reputation point to whom you think deserves it, a green square appears for a reputation with more and more adding over time.Pretty soon you will see a string of green squares appear in the top right of your posts as your reputation grows and your forum presence!

Good Luck!
The Hulkster!

Reply
Page 1 of 2 1 2 >
 
Thread Tools Search this Thread Display Modes
  #1  
Old 07-14-2008, 03:41 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
Date cells not being identified as dates !
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.
Code:
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.
Code:
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:
Code:
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 . . .
Attached Files
File Type: xlsx IRC Inactivity .xlsx (14.7 KB, 1 views)
Last edited by Tuche; 07-14-2008 at 03:51 PM.
Reply With Quote
  #2  
Old 07-14-2008, 03:51 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
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...
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Reply With Quote
  #3  
Old 07-14-2008, 03:57 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
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
Reply With Quote
  #4  
Old 07-14-2008, 04:02 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
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...
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Reply With Quote
  #5  
Old 07-14-2008, 04:11 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
=TODAY()-DATEVALUE("Apr 19, 2008")

gives me #VALUE!

weird, why can you not see my formulas of B and D ?
Reply With Quote
  #6  
Old 07-14-2008, 04:18 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
in xls (97) it breaks everything, too many levels of nesting . . .

on xlsx B2,
Code:
=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,
Code:
=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;","));""))
Reply With Quote
  #7  
Old 07-14-2008, 04:19 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
Quote:
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...
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Reply With Quote
  #8  
Old 07-14-2008, 04:22 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
Hum that is weird, what could be causing the problem with TODAY()-DATEVALUE(Apr 22, 2008) ?

Any option ?
Reply With Quote
  #9  
Old 07-14-2008, 04:24 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
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...
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Reply With Quote
  #10  
Old 07-14-2008, 04:38 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
Yes, i did.

To test, i just created a new blank document.

In the first cell, i enter . . .

Code:
=TODAY()-DATEVALUE("Apr 19, 2008")
and get #VALUE!

What is going wrong here
Reply With Quote
  #11  
Old 07-14-2008, 04:40 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
What happens if you use this instead?

=TODAY( )-"Apr 19, 2008"+0
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Reply With Quote
  #12  
Old 07-14-2008, 04:46 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
#VALUE !

something is really screwed here . . .

do you think a reinstall would fix it ?
Reply With Quote
  #13  
Old 07-14-2008, 04:49 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
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
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Last edited by NBVC; 07-14-2008 at 04:51 PM.
Reply With Quote
  #14  
Old 07-14-2008, 04:51 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is on a distinguished road
Yes, i did exactly that.

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

Reply With Quote
  #15  
Old 07-14-2008, 04:55 PM
NBVC's Avatar
NBVC NBVC is online now
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
Posts: 7,612
NBVC will become famous soon enough NBVC will become famous soon enough
Try opening the attached and see what you get in cell A1.
Attached Files
File Type: xls Book1-Jul14.xls (13.5 KB, 7 views)
__________________
Where there is a will there are many ways. Finding one that works for you is the challenge!

Please read the Forum Rules: Rules
Reply With Quote
Reply
Page 1 of 2 1 2 >

Bookmarks

New topics in Excel 2007 Help

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump


All times are GMT -4. The time now is 12:21 AM.

Contact Us - ExcelTip.com - Archive - Top

Powered by vBulletin® Version 3.7.3
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0