Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove an item from a list
I'm having a problem with a colum in my worksheet. the dates in this
format 06-SEP-2006 14:34:57 EST, which is causing an error in my calculations. I want to remove the EST from the whole list, is there a quick way I can do this as my reports come everymonth with over 100 entrys. Please help!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove an item from a list
Hi Princy,
Your problem is that the EST is causing the date to behave as text, so you can't calculate on it. Assuming that your dates are constant (ie the days and hours/minutes/seconds) are always two digits like in your example, the following formula will work: =VALUE(LEFT(A1,21)) - substitute A1 for the cell your date is in It takes the leftmost 21 characters (thus eliminating the EST which is causing your problem and converts them to a date/time number (that's what the =VALUE at the front of the formula is doing) The result will be a number (in this case 38966.06076). All you need to do then is format the cells as custom dd-mmm-yyyy hh:mm:ss and the result will be 07-Sep-2006 14:34:57 Hope this helps regards Phil wrote: I'm having a problem with a colum in my worksheet. the dates in this format 06-SEP-2006 14:34:57 EST, which is causing an error in my calculations. I want to remove the EST from the whole list, is there a quick way I can do this as my reports come everymonth with over 100 entrys. Please help!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove an item from a list
hey Phil
Thanks for that but I dont know why but its coming up with a value error? Can you help? Thanks Princy Phil wrote: Hi Princy, Your problem is that the EST is causing the date to behave as text, so you can't calculate on it. Assuming that your dates are constant (ie the days and hours/minutes/seconds) are always two digits like in your example, the following formula will work: =VALUE(LEFT(A1,21)) - substitute A1 for the cell your date is in It takes the leftmost 21 characters (thus eliminating the EST which is causing your problem and converts them to a date/time number (that's what the =VALUE at the front of the formula is doing) The result will be a number (in this case 38966.06076). All you need to do then is format the cells as custom dd-mmm-yyyy hh:mm:ss and the result will be 07-Sep-2006 14:34:57 Hope this helps regards Phil wrote: I'm having a problem with a colum in my worksheet. the dates in this format 06-SEP-2006 14:34:57 EST, which is causing an error in my calculations. I want to remove the EST from the whole list, is there a quick way I can do this as my reports come everymonth with over 100 entrys. Please help!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Remove an item from a list
The value error occurs when Excel thinks it's working with text rather
than numbers. If you like I'll send you a spreadsheet in which I've got it to work - it might be there's someting else in yours that's causing the problem. Incidentally, I'll send an answer to your other post shortly Regards Phil HTH Phil On Oct 20, 3:29 pm, " wrote: hey Phil Thanks for that but I dont know why but its coming up with a value error? Can you help? Thanks Princy Phil wrote: Hi Princy, Your problem is that the EST is causing the date to behave as text, so you can't calculate on it. Assuming that your dates are constant (ie the days and hours/minutes/seconds) are always two digits like in your example, the following formula will work: =VALUE(LEFT(A1,21)) - substitute A1 for the cell your date is in It takes the leftmost 21 characters (thus eliminating the EST which is causing your problem and converts them to a date/time number (that's what the =VALUE at the front of the formula is doing) The result will be a number (in this case 38966.06076). All you need to do then is format the cells as custom dd-mmm-yyyy hh:mm:ss and the result will be 07-Sep-2006 14:34:57 Hope this helps regards Phil wrote: I'm having a problem with a colum in my worksheet. the dates in this format 06-SEP-2006 14:34:57 EST, which is causing an error in my calculations. I want to remove the EST from the whole list, is there a quick way I can do this as my reports come everymonth with over 100 entrys. Please help!!- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto updating list | Excel Worksheet Functions | |||
Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
How to Change List Based on Value Chosen in Another List | Excel Worksheet Functions | |||
Help! Selecting item on data validation list switches windows | Excel Discussion (Misc queries) | |||
finding lowest priced item in a list | Excel Discussion (Misc queries) |