Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    I have two columns of data which come from a copied/pasted a list of appt data from outlook 2010 to excel 2010 that looks like this.
    6:30:00 AM 6:59:00 AM
    7:05:00 PM 8:40:00 PM

    I want to have a third column which provides the difference between those two times that I can then add up at the bottom of the columns so I know how much time I've spent during the day on stuff. How do I do this? I've tried just subtracting and playing with various date/time functions

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Assume:
    1) times are in A2 and B2
    2) times are all on same day
    3) looking for answer in minutes
    Try this in C2.....
    =((B2-A2)*24)*60

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    The attached will calculate time spent in Hours and Minutes.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    This is great and exactly what I want. However it doesn't work with my data pasted from Outlook. I get a #VALUE as a result. The data looks the same but it must not be (although I did format them both to general and the start time for yours and mine both show 0.270833333333333. I even tried to format the columns as time and then as the same custom format as your file showed h:mm AM/PM.


    Quote Originally Posted by Tim Sullivan View Post
    The attached will calculate time spent in Hours and Minutes.

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Can you attach an example of yours with the error #VALUE.

    Thanks,
    Tim

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    OK. So I started over and this time copied data from the Outlook Journal instead of the Calendar, and it worked! Thanks so much. So now I'm excited to try the next step. I have the first data like I mentioned before, but there are more columns

    Type Category Start End Duration
    Task e-Myth Systems 6:30:00 AM 6:49:00 AM 19 minutes
    Task e-Myth Leadership 7:00:00 AM 7:05:00 AM 5 minutes
    Task Personal 7:15:00 AM 8:45:00 AM 1.5 hours
    Task e-Myth Systems 8:45:00 AM 9:10:00 AM 25 minutes

    I want to on a different sheet be able to group this time log by day by category. Now, I can send the day/time instead of just time from Outlook by changing the format of the list view in Outlook (or perhaps it's embedded anyway in that time?) but my final Excel row should look something like.

    Category Day1 Day2 Day3 .... Average time spent per category

    I've attached the spreadsheet I'm starting with that has this as the "Time Log Summary" sheet.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Thanks for the example workbook.
    On the tab named "Daily Time Log" in the Column named "Duration" it looks like the data is input manually.
    To automate that process, try this formula beginning in cell E8 and copy down.......
    =IF(ROUND(((D8-C8)*24)*60,0)<60,ROUND(((D8-C8)*24)*60,0)&" minutes",ROUND(((D8-C8)*24),2)&" hours")

    There are several approaches for the Summary.
    How many Categories will there be?
    Will there be only 5 days of info?
    It looks like the goal is 5 day average for Category and Category % by day?

    For sure there will be more questions !!

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Thanks Tim,

    You're right that that data for Duration isn't a formula but it's part of what's copied from Outlook and I decided not to use it as I didn't know how to add it up. But I figured there'd be a way to calculate the duration and then those columns could be summarized for the summary sheet. You provided those formulas thank you very much. So, to answer your questions for the summary, there are currently 9 categories, but that could change. There are currently 5 days to track per week and that's just fine to say will stay stable. Some of those fields already have simple formulas to do exactly what you describe below.

    My goal is to be able to grab the data from the first sheet and instead of having to manually summarize and populate, for the day actuals for each category to be filled in automatically.

    Thanks!

    Quote Originally Posted by Tim Sullivan View Post
    Thanks for the example workbook.
    On the tab named "Daily Time Log" in the Column named "Duration" it looks like the data is input manually.
    To automate that process, try this formula beginning in cell E8 and copy down.......
    =IF(ROUND(((D8-C8)*24)*60,0)<60,ROUND(((D8-C8)*24)*60,0)&" minutes",ROUND(((D8-C8)*24),2)&" hours")

    There are several approaches for the Summary.
    How many Categories will there be?
    Will there be only 5 days of info?
    It looks like the goal is 5 day average for Category and Category % by day?

    For sure there will be more questions !!

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    There are several ways to get to a Summary. Take a look at the attached. Using this method, the daily data would be placed on the appropriate "Day #" tab.

    Any thoughts?
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post
    Seeet!!

    I think this is everything I need. I'll work with it for the next week, and also study the formulas. thank you so much!

    Bryan

    Quote Originally Posted by Tim Sullivan View Post
    There are several ways to get to a Summary. Take a look at the attached. Using this method, the daily data would be placed on the appropriate "Day #" tab.

    Any thoughts?

Posting Permissions

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