Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 23rd 05, 07:05 PM
Dan
 
Posts: n/a
Default Excel not recognizing Dates properly.

I have a spreadsheet that has a column of dates exported
from a website. The user is trying to add 1 day to the
date and put it in another colunm...

ei: =A1 + 1

This works fine for most of the however, some of the give
the #value error. After playing around with them I
realized that Excel seems to be treating the dates as
strings, when I try to format them as numbers or dates,
they never change. Seems that all the dates between month
1 and Month 4 dont change but all the other dates get
formated correctly. Now the real tricky part comes next.
If I put my cursor in the field (as if I am editing it)
then press enter or 'tab' out Excel seems to then see it
as a date and formats it properly...

ie. Value is 3/16/2005. I enter the field, press tab, and
once focus is gone from that cell it gets formated
correctly.

This isnt really a pressing issue, but I must be missing
something really simple.

  #2   Report Post  
Old March 23rd 05, 07:19 PM
JE McGimpsey
 
Posts: n/a
Default

This often happens with data pasted in from web sites. To coerce the
"text numbers" into numbers, copy an empty cell. Select your text
numbers, then choose Edit/Paste Special, selecting the Values and Add
radio buttons. Click OK. You will probably then need to reformat...


In article ,
"Dan" wrote:

I have a spreadsheet that has a column of dates exported
from a website. The user is trying to add 1 day to the
date and put it in another colunm...

ei: =A1 + 1

This works fine for most of the however, some of the give
the #value error. After playing around with them I
realized that Excel seems to be treating the dates as
strings, when I try to format them as numbers or dates,
they never change. Seems that all the dates between month
1 and Month 4 dont change but all the other dates get
formated correctly. Now the real tricky part comes next.
If I put my cursor in the field (as if I am editing it)
then press enter or 'tab' out Excel seems to then see it
as a date and formats it properly...

ie. Value is 3/16/2005. I enter the field, press tab, and
once focus is gone from that cell it gets formated
correctly.

This isnt really a pressing issue, but I must be missing
something really simple.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I select unique dates from a database in Excel? pomalley Excel Worksheet Functions 3 March 9th 05 03:02 PM
How do I keep excel 2002 from changing fractions to dates? pertimesco Excel Discussion (Misc queries) 6 March 1st 05 01:01 AM
Re-formatting of dates from Excel to CSV GLS Excel Discussion (Misc queries) 2 February 7th 05 12:42 PM
Why does Excel Copy and Paste change dates from the original when. Excel changes dates in copy and paste Excel Discussion (Misc queries) 5 January 26th 05 05:08 PM
have dates entered from a list of data into an excel template Diane Excel Discussion (Misc queries) 1 December 28th 04 06:33 PM


All times are GMT +1. The time now is 07:43 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017