Formula not reading date format
 

Hello:

Based on a person's "available date" I need to categorize that person as
"0-30 days," "31 to 60 days," or " 60 days." Below is the formula I'm using:

=IF(B2(TODAY()+60)," 60",IF(B2(TODAY()+30),"31 to 60","0 to 30"))

The problem is that my formula does not read the date format in the file
that I get. I have to go into each date, hit F2 and enter, and then the
formula reads it. Below is the worksheet setup

Initially....

A B C
1 Name Avail Date Range
2 Larry 2010-05-31 60
3 Curly 2010-07-01 60
4 Moe 2010-8-25 60

After re-formatting each cell (F2, enter)....

A B C
1 Name Avail Date Range
2 Larry 5/31/2010 0 to 30
3 Curly 7/1/2010 31 to 60
4 Moe 8/25/2010 60

I have tried changing the cell format, but the only thing that works is if I
change each record individually. I have to do this for over 500 records!
And it's a weekly thing.

Is there a faster way? Please say yes...thanks!!

Formula not reading date format
 

Sometimes this will work...

Select the range of dates
Goto the menu DataText to Columns
Click Next twice
In step 3 of the wizard under column data format select Date and from the
drop down select YMD

Click Finish

--
Biff
Microsoft Excel MVP


"ziak" wrote in message
...
Hello:

Based on a person's "available date" I need to categorize that person as
"0-30 days," "31 to 60 days," or " 60 days." Below is the formula I'm
using:

=IF(B2(TODAY()+60)," 60",IF(B2(TODAY()+30),"31 to 60","0 to 30"))

The problem is that my formula does not read the date format in the file
that I get. I have to go into each date, hit F2 and enter, and then the
formula reads it. Below is the worksheet setup

Initially....

A B C
1 Name Avail Date Range
2 Larry 2010-05-31 60
3 Curly 2010-07-01 60
4 Moe 2010-8-25 60

After re-formatting each cell (F2, enter)....

A B C
1 Name Avail Date Range
2 Larry 5/31/2010 0 to 30
3 Curly 7/1/2010 31 to 60
4 Moe 8/25/2010 60

I have tried changing the cell format, but the only thing that works is if
I
change each record individually. I have to do this for over 500 records!
And it's a weekly thing.

Is there a faster way? Please say yes...thanks!!

Formula not reading date format
 

SWEET!!!!!!!!!

Thanks, Biff -- a true Excel MVP!

"T. Valko" wrote:

Sometimes this will work...

Select the range of dates
Goto the menu DataText to Columns
Click Next twice
In step 3 of the wizard under column data format select Date and from the
drop down select YMD

Click Finish

--
Biff
Microsoft Excel MVP


"ziak" wrote in message
...
Hello:

Based on a person's "available date" I need to categorize that person as
"0-30 days," "31 to 60 days," or " 60 days." Below is the formula I'm
using:

=IF(B2(TODAY()+60)," 60",IF(B2(TODAY()+30),"31 to 60","0 to 30"))

The problem is that my formula does not read the date format in the file
that I get. I have to go into each date, hit F2 and enter, and then the
formula reads it. Below is the worksheet setup

Initially....

A B C
1 Name Avail Date Range
2 Larry 2010-05-31 60
3 Curly 2010-07-01 60
4 Moe 2010-8-25 60

After re-formatting each cell (F2, enter)....

A B C
1 Name Avail Date Range
2 Larry 5/31/2010 0 to 30
3 Curly 7/1/2010 31 to 60
4 Moe 8/25/2010 60

I have tried changing the cell format, but the only thing that works is if
I
change each record individually. I have to do this for over 500 records!
And it's a weekly thing.

Is there a faster way? Please say yes...thanks!!









.

Formula not reading date format
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ziak" wrote in message
...
SWEET!!!!!!!!!

Thanks, Biff -- a true Excel MVP!

"T. Valko" wrote:

Sometimes this will work...

Select the range of dates
Goto the menu DataText to Columns
Click Next twice
In step 3 of the wizard under column data format select Date and from the
drop down select YMD

Click Finish

--
Biff
Microsoft Excel MVP


"ziak" wrote in message
...
Hello:

Based on a person's "available date" I need to categorize that person
as
"0-30 days," "31 to 60 days," or " 60 days." Below is the formula I'm
using:

=IF(B2(TODAY()+60)," 60",IF(B2(TODAY()+30),"31 to 60","0 to 30"))

The problem is that my formula does not read the date format in the
file
that I get. I have to go into each date, hit F2 and enter, and then
the
formula reads it. Below is the worksheet setup

Initially....

A B C
1 Name Avail Date Range
2 Larry 2010-05-31 60
3 Curly 2010-07-01 60
4 Moe 2010-8-25 60

After re-formatting each cell (F2, enter)....

A B C
1 Name Avail Date Range
2 Larry 5/31/2010 0 to 30
3 Curly 7/1/2010 31 to 60
4 Moe 8/25/2010 60

I have tried changing the cell format, but the only thing that works is
if
I
change each record individually. I have to do this for over 500
records!
And it's a weekly thing.

Is there a faster way? Please say yes...thanks!!









.