Subtract text/date columns

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

The attached will calculate time spent in Hours and Minutes.

Reply | Quote

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.

The attached will calculate time spent in Hours and Minutes.

Reply | Quote

Can you attach an example of yours with the error #VALUE.

Thanks,
Tim

Reply | Quote

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.

Reply | Quote

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 !!

Reply | Quote

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!

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 !!

Reply | Quote

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?

Reply | Quote

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

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?

Reply | Quote