Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell format on reading .csv file | Excel Discussion (Misc queries) | |||
Copnvert dates reading as Text to a date format | Excel Discussion (Misc queries) | |||
Reading File Name in VB Macro that contains today's date | Excel Discussion (Misc queries) | |||
date reading formula question | Excel Worksheet Functions | |||
Reading Date Formats e.g 20050801 (yyyy/mm/dd) | Excel Discussion (Misc queries) |