Full Version: Ditto and Date madness
Squire_King
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.
KingMartin
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
Squire_King
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
NateO
=IF(LEN(TRIM(A2))=0,"",IF(C2="""",F1,--(MID(C2,4,2)&"/"&LEFT(C2,2)&"/"&RIGHT(SUBSTITUTE(SUBSTITUTE(C2,"/",""),".","")))))
Squire_King
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
NateO
Hello, odd, worked for me. Euro date problem?
Squire_King
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
NateO
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.
Squire_King
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...??

Stu
NateO
2 is the length:

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

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

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

Yeah, not too sure what the deal is here...
Squire_King
QUOTE
2 is the length:

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

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(...." ?

Cheers

Stu
NateO
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.

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

I'm not sure what the question is. This converts the concatenated results from text to a number.
Squire_King
Great, thanks Nate.

Have a great evening!!

Signing off now

Stu
Squire_King
Hi,

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

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

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"?
NateO
Yeah, today is: 38133

Why: Left(--,2) ?
Squire_King
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