Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    How have times before 10 AM been entered? As for example 0930 or as 930?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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)!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •