Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Outlook Dates/Times (XP)

    I am trying to either export or paste appointment start and end times so that I can calculate the duration and get a subtotal.

    I can paste the start and end dates but when I try to calculate the duration I get #value errors.

    I can also display a duration field in Outlook and display that, but get 0 when I subtotal.

    I'm assuming Excel is not reading the data as numeric, but how would I go about fixing that? I've attached a sample file.

    Any suggestions will be appreciated.
    Attached Files Attached Files
    egghead

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Outlook Dates/Times (XP)

    You could try this (somewhat) long-winded solution, provided you're prepared to tackle it with a macro.

    1. Trim the first four characters off each entry (i.e. the date and the trailing space), by wrapping each in a =MID("cell ref",5,20) expression.
    2. Tack on the difference formula that you've already got in your spreadsheet and copy the resulting amended table so that you have two blocks ("A" and "B").
    3. Take the values in the first two columns of "A" and wrap each of them in a =TIMEVALUE("your appointment") expression - not forgetting to use the quotation marks. This will give you values in hours and minutes.
    4. Take the values in the first two columns of "B" and wrap each of them in a =DATEVALUE("your appointment") expression. This will - funnily enough - give you values in hours.
    5. Amalgamate "A" and "B", converting into days and cleaning up as necessary.

    HTH
    Gre

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Outlook Dates/Times (XP)

    With that day name on the left, Excel is interpreting those as text, not dates. If you are copying those dates one at a time, then you could copy them without the day name on the front.

    You could also put the following formula into H2 and then fill it to I5. That should give you the date/times without the day name. Then copy H2:I5 and Paste Special/Values back over D2:E5.

    <pre>=RIGHT(D2,LEN(D2)-4)
    </pre>


    If you have to do this often, you could write a macro to remove the day name.
    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Outlook Dates/Times (XP)

    Your formula worked great. However, I don't know that I can easily teach the end users how to do this, and I'm not very proficient at macros.

    There is no way to paste the data without the day of the week, but there is a field called "duration" that shows minutes and hours: 30 minutes, 48.5 hours, etc. Do you know of a similar formula we could use on that field?

    Maybe if I could design a macro for that . . .

    Thanks for what I've learned already! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
    egghead

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Outlook Dates/Times (XP)

    The macro below will remove the first four characters of all the cells in the current selection. You could paste in the date/times and then select the cells and run this macro.

    <pre>Public Sub RemoveDay()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = Right(oCell.Value, Len(oCell.Value) - 4)
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Outlook Dates/Times (XP)

    Awesome! This will work great in a template AND be easy to explain! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Can you tell me what "ocell" means? Thanks a lot.
    egghead

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Outlook Dates/Times (XP)

    oCell is just a variable name I dreamed up. The "o" is a convention I use to indicate that a variable is an object variable. "Cell" indicates that it is used as a range object that is a cell on the worksheet.
    Legare Coleman

Posting Permissions

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