Me again,

We have some customers sending in sheets for processing and the guys in the office are spending ages trying to re format. (please see attached)

1 - Particularly dates that have a dot or dash at the end. What's the best way to get these re-formatted ...some sort of Case structure ?? Replace Right , 1, with "" if it's a dot or dash ?


2 - The other pain is those Users putting a ", as in DITTO, for dates. Is there a way to replace the " with the offset of (-1,0) ..sort of thing ?? ...being mindfull that there may be several in succession.



Any help with above appreciated.


Stu




Edited by: Squire_King on 05.25.04.

Hello Stu,

the following:

=DATEVALUE(LEFT(C6,8))

seems to work for most of your data. Embed in IF(C6="","",DATEVALUE(LEFT(C6,8))) if you need to handle the Empty Strings.

Cheers

Martin

Hi Martin,

Couldn't get that to work unfortunately ...just get the #VALUE

but have now resolved with

=IF((C6=""),"", (IF(OR(RIGHT(C6,1)=".",RIGHT(C6,1)="/"), LEFT(C6,8),C6)))

....as I had some other formating first.


If you can help with the second part, that would be good


Stu

=IF(LEN(TRIM(A2))=0,"",IF(C2="""",F1,--(MID(C2,4,2)&"/"&LEFT(C2,2)&"/"&RIGHT(SUBSTITUTE(SUBSTITUTE(C2,"/",""),".","")))))

Hi Nate,

It must be me...I've put your formula in Cell F1 and dragged down and get #VALUE if there is something in Col A.

Please can you let me know where I've been dozy.

Ta

Hello, odd, worked for me. Euro date problem?

Thanks Nate,


That is really odd.

I opened your file and all looks perfect.

Clicked on the = sign in the formula bar to see what's 'going on' and it returns #VALUE

Then when I click the undo button ALL cells with the formula return #VALUE


Seen this sort of thing before???



Stu

Has to be your regional Windows settings. I'm processing as mm/dd/yy, then format the cells. It seems that you must process as dd/mm/yy.

At least they're consistent with xx/xx.

Possible...

A few questions ...if you have the time, as I played with it for a while ...not sure what

1 - the -- is doing

2 - why there is no length required for the " &RIGHT..." part ...as in Right(cellref,length)


Changed the code to :-

=IF(LEN(TRIM(A2))=0,"",IF(C2="""",F1,(LEFT(C2,2)&"/"&MID(C2,4,2)&"/"&RIGHT((SUBSTITUTE(SUBSTITUTE(C2,"/",""),".","")),2))))

And this worked

Feeling happy, I then input a standard 13/04/2004 type date and got 38/00/00


I suppose I could have a few nested IFs ...if dot or dash at end do a MID, else take the RIGHT 2...??


Your thoughts appreciated.


Stu

2 is the length:

RIGHT((SUBSTITUTE(SUBSTITUTE(C2,"/",""),".","")),2)

--x coerces x from a text string to a serial date.


Yeah, not too sure what the deal is here...

The "2" is there in my adjusted version ... not in your original ... but it still worked!! ??

Is this what the two dashes are doing ....",IF(C2="""",F1, -- (MID(...." ?



Thanks for your support.


Cheers


Stu

Ah yes, right(): If num_chars is omitted, it is assumed to be 1. So it's pulling 4, which it assumes to be 2004.


I'm not sure what the question is. This converts the concatenated results from text to a number.

Great, thanks Nate.

Have a great evening!!


Signing off now





Stu

Hi,


re

It seems that when the date is like 13/04/2004, when trying to get the Left(--,2) ...the "38" is part of the numeric value of the date

......strange, don't remember this happening on other projects. ....is there a way round, to always get, in this example, "13"?

Yeah, today is: 38133

Why: Left(--,2) ?

Sorry, me being lazy ....I had put -- instead of the cell ref, like C2.


Interesting that, as part of my reformating process on these external sheets, I had been using Text To Columns first...and for the this one, clicking on Date. Then when trying to tweak the format, i.e. for dittos and dots etc...it got very messy.

However, by not using text to columns first, your formula worked and I didn't get the '38' part of the numeric date.

v.strange.


All sorted now.

Thanks