Hi. I have a problem !! [No, not that one ! ] Basically, I’m not a programmer, and need to find someone with some [ fairly basic] programming skills, which are really more in the nature of batch files I suppose. Here are the details:-



MS, in their infinite wisdom no longer support any of the programs I have bought and paid for, but they are old ones, so maybe that’s OK.
Whatever the reason, I am now forced to abandon XP, and also Office 2000, which I have always used.

So I got a laptop with Windows 7, which is very good. My problem is that Outlook 2000 won’t run on it. A well documented problem, I’ve found out. OK. Word and Excel are OK, so it’s just the PIM side of Outlook that I use. Thunderbird for emails is great.

So I’ve tried several PIMS, [currently like EssentialPIM], but it [ and most of them] won’t import from Outlook 2000.
They don’t read the .pst backup file. They can import a CSV file for contacts, and I’ve done that. The problem is the CALENDAR side, for Appointments etc. They only import from ICAL format. However, I can convert the Calendar.csv file to Ical through various conversion facilities.

However, in the course of this, I now find that all the dates in my Excel . CSV file that begin with a single digit [ as Excel always drops a leading 0,] have transposed the day with the month. So what should be 09/01/2011 has become 1/09/2011. And 05/08/2012 would become 8/05/2011.

Don’t ask me how or why, as it took me long enough just to discover what was happening, as you can imagine.!!

So if we call the Date field a 10 digit field, counting the separators as a digit, then basically the 1st digit [when 0 is being dropped, or the second if that doesn’t matter, ] then the 1st [ visible]and 5th digits are being transposed.
I have edited the first 200 or so manually, but as I have nearly 2000 to do, I thought there ‘must be an easier way”.
[Story of my life]!!

So whether I have to select the cells involved manually, or the script can start “Where the date field is 9 digits, or the first sub-field before the first separator contains only a single digit……etc”, it doesn’t really matter. Actually, on second thoughts it does, as I don’t need the ones I’ve already changed, changed back !

The important thing is that what runs can transpose the 5th and 2nd digits [ assuming the first zero has been dropped. Otherwise it becomes the 1st digit in a 9 digit field ?

I think !!

It wouldn’t even help me to buy Outlook 2013, as I’m sure the same thing will apply. It can’t import from Outlook 2000, and in any case, Outlook and Excel between them have totally screwed up the CSV exporting possibilities. And MS don’t support 2000 anymore. Or, and more to the point, their loyal customers !

ANY IDEAS ??

Hi
as you have given no clue to your location and thus to which your regional settings are ( mm/dd/yy or dd/mm/yy),try it this way:
select the column containing the dates
Data tab - text to columns
Check "delimited"
Click Next twice
Select "Date"
Change the drop down box to MDY
Finish
If it does not work, try variations with the dropdown box.

( giving your approximate location in forum CP helps)

__________________
Using O365 v2407 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi. Thanks for the reply. As I have only just joined, and entered Auckland as my Time Zone, that means I'm in New Zealand, which uses the 'European' date format of DD/MM/YYYY. The 4 digits for the year seems to satisfy all potential import receiving programs, hence you will see that used in the body of my query.

But you are right. I didn't 'spell it out'. As for 'no clue'.......? I would have thought there were 'clues' aplenty ! But I should have said.

So thanks for your suggestion, and I will try that. But the problem, as I have stated, is only when there is a single digit in the DAY part of the date field. The first 2 characters before the first separator. The problem only occurring when the first character is a zero, which Excel drops in a .csv file.

But I'll give it a go.
Thanks,

Hi Pecoflyer.
Thanks so much for your suggestions ! I have to say that I had 'mixed' results. I tried it on 3 different spreadsheets, 2 of which were slightly different , and 1 backup.

The first 2 I tried showed no change, but the 3rd one did ! All the dates on that one displayed as they should......and also imported correctly after conversion to Ical format.
So thanks a million. No idea why 1 worked and not the others.

It would be easy to say that 'Computing is not an exact science'.......but I believe that it is ! [That's only for the customers ]!

On the other hand, there are many things that can make a difference, such as memory usage, what else is open, mistyping etc etc.

But the bottom line is that I got what I needed, so thanks so much. You are a star ! Not least, for bothering to reply to my cry for help.

Cheers for now.
S-M