Problem with Dates
Posted by John B on June 15, 2001 10:20 AM
I am copying data from Access. There are a few columns that contain dates. When I format the cells, two of the columns (columns D and E) come back okay but one (column C)returns strange dates (ie 121698 returns 3/12/33). My problem is I have a formula to see if the dates (in column C) fall between the other dates (in D and E). Is there any way to change column C or columns D and E to be able to still check to see if the dates in C fall between D and E?
Thanks!
Posted by Barrie Davidson on June 15, 2001 10:44 AM
How is your data (for column C) formatted in Access? How are you copying the data from Access (straight cut and paste or are you exporting)?
Barrie
Posted by John B on June 15, 2001 11:39 AM
It is formatted as text. I tried to change the formatting in Access but it returned an error stating that data would be deleted.
I have been using the striaght cut and paste method.
Thanks!
Posted by Barrie Davidson on June 15, 2001 11:45 AM
Here's what I would do (hopefully this will work for you):
Use another column for my calculations instead of column C, say column M to demonstrate. In M1 I would put the following formula:=DATE(RIGHT(C1,2),MID(C1,1,2),MID(C1,3,2))
This will convert the number in C1 to a date that can be used for your calculations. You would then copy the formula to the bottom of your data range.
Regards,
Barrie
Posted by John B on June 15, 2001 12:33 PM
Your solution works for all the dates that are from 1998. For dates with another year it still is acting up(ie 2/24/99 goes to 11/18/00). I could sort them and change the formula for each year but I am not sure how to change the formula. I have dates ranging from 1997 to 2000.
I have over 400,000 cases and this seemed like an overwelming task. Thanks for all your help!!
Posted by Barrie Davidson on June 15, 2001 12:57 PM
Okay, my mistake, I think I understand. If your month and/or day is only one number (e.g., January = 1 and not = 01) then this formula won't work. How would you be able to tell 11298(Jan 12, 1998) from 11298 (Nov 2, 1998)?
Barrie
Posted by John B on June 15, 2001 1:12 PM
Okay, my mistake, I think I understand. If your month and/or day is only one number (e.g., January = 1 and not = 01) then this formula won't work. How would you be able to tell 11298(Jan 12, 1998) from 11298 (Nov 2, 1998)?
The leading zeros have been dropped off so 11298 would be Jan 12, 98. Nov 2, 1998 is showing as 110298. Would formatting the cells to show the leading zero help?
Posted by Barrie Davidson on June 15, 2001 1:24 PM
Formatting
As long as the days have leading zero (110298 for Nov 2, 1998 and 10298 for Jan 2, 1998) then this formula should work for you.
=IF(LEN(C1)=6,DATE(RIGHT(C1,2),MID(C1,1,2),MID(C1,3,2)),DATE(RIGHT(C1,2),MID(C1,1,1),MID(C1,2,2)))
Barrie
Posted by John B on June 15, 2001 3:03 PM
Re: Formatting (Thanks!)
Thanks Barrie, that worked great!
