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

Go Back   Excel Help Forum > Microsoft Office Application Help - Excel Help forum > 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

Reply
Page 2 of 2 < 1 2
 
Thread Tools Search this Thread Display Modes
  #16  
Old 07-14-2008, 05:03 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is an unknown quantity at this point
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 . . .
Reply With Quote
  #17  
Old 07-14-2008, 05:53 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is an unknown quantity at this point
Quote:
Originally Posted by NBVC
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 ?
Reply With Quote
  #18  
Old 07-14-2008, 06:02 PM
NBVC's Avatar
NBVC NBVC is offline
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 8,550
NBVC is just really nice NBVC is just really nice
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...
__________________
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
  #19  
Old 07-14-2008, 06:08 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is an unknown quantity at this point
That last one worked . . . 86 !

Now i need to find a way to fix my dates in my file . . . *wonders*
Reply With Quote
  #20  
Old 07-14-2008, 06:11 PM
daddylonglegs's Avatar
daddylonglegs daddylonglegs is offline
Forum Moderator
  
Join Date: 14 Jan 2006
Location: England
Posts: 5,172
daddylonglegs is a jewel in the rough
Quote:
Originally Posted by Tuche
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
Reply With Quote
  #21  
Old 07-14-2008, 06:21 PM
Tuche Tuche is offline
Registered User
  
Join Date: 14 Jul 2008
Location: Hamburg, Germany
Posts: 12
Tuche is an unknown quantity at this point
Thanks daddylonglegs,

are you sure that formula is correct, my head hurts already . . .
Reply With Quote
  #22  
Old 07-15-2008, 10:41 AM
NBVC's Avatar
NBVC NBVC is offline
Forum Moderator
  
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 8,550
NBVC is just really nice NBVC is just really nice
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....
__________________
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 2 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 01:22 AM.

ExcelTip.com - Archive - Top

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