dates in 'text' format need to be put in proper order for importing to outlook

Topic

New Reply

My spreadsheet lists many events, which I hope to import to a shared Outlook calendar. I think that if I put it into the proper format, the import should work smoothly. However, the originator of the spreasheet identified the time of a meeting using just one cell containing e.g. ‘ 1000 – 1300 ‘ in text, meaning 10am to 1pm.
When I text-to-colunm this, I get two columns with the contents still in the text (or general) format, and can’t make it work as a time – which I suppose it needs to be in order for Outlook to know the StartTime and EndTime for the event once imported.
Any help with this would be most appreciated. Thanks Frank

Reply | Quote

Replies

How have times before 10 AM been entered? As for example 0930 or as 930?

Reply | Quote

All times have four digits, 0900 or 0830 etc. in a few cases, a period is included 09.00 but never a colon.
Also there may or may not be spacing around the hypen, or only a single four digit time is included ie no end time.
Thanks Hans, it feels good to have you on the case.

Reply | Quote

So the entries are not consistent. That makes it difficult to come up with an automated solution. I’d force the originator of the workbook to enter the start and end times correctly by hand, that’ll teach him (or her)!

Reply | Quote

You could use a series of formulas to calculate the times, as in the attached sample workbook.
They should return correct start and end times for most situations
You’ll have to inspect the result visually and manually correct the exceptions.

Reply | Quote

Hans, thank you very much for this, I shall try it out.
Unfortunately, the originator did not know we were going to import into Outlook, and would not have known the correct format to use in any case.
Plus this is a one-off and *should* never have to be done again
Frank

Reply | Quote