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!!

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!!

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!!

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 -