Sum Time

W

womblew

Here is the set up.

Begin TimeEnd Time Total Time on call
7:00:00 17:15:00 10:15:00
8:00:00 14:00:00 6:00:00
8:30:00 17:00:00 8:30:00
7:15:00 16:20:00 9:05:00
9:55:00 16:30:00 6:35:00
7:00:00 9:00:00 2:00:00
8:15:00 9:35:00 1:20:00
7:00:00 8:15:00 1:15:00
13:00:00 14:30:00 1:30:00
13:30:00 14:50:00 1:20:00
6:30:00 16:00:00 9:30:00
8:15:00 15:50:00 7:35:00
10:00:00 14:30:00 4:30:00
8:00:00 11:00:00 3:00:00
7:00:00 13:00:00 6:00:00
8:00:00 16:00:00 8:00:00
8:00:00 13:00:00 5:00:00
13:00:00 16:00:00 3:00:00
8:00:00 12:00:00 4:00:00
14:00:00 15:00:00 1:00:00
12:30:00 16:45:00 4:15:00

I am trying to sum the total number of hours and minutes in Total time.
Begin and End Time are input as :mm/dd/yyyy hh:mm:ss AM/PM. Total Time is
formatted as hh:mm:ss. Begin Time is subtracted from End Time for Total time
on call.

When I so auto sum for the Total Time at the bottom, I get 7:40:00. I know
there are more hours than 7. what am I doing wrong.
 
T

T. Valko

Format your total formula cell as [hh]:mm

The brackets [ ] keep the hours from rolling over into days after 24 hours.
For example:

36:00

Formatted as hh:mm = 12:00
Formatted as [hh]:mm = 36:00
 
B

Bernard Liengme

Try formatting the cell with the total with [hh]:mm:ss
Tell us if this works
best wishes
 
W

womblew

This is not work It came back with "9103:40:00". I know that there were not
9103 minutes there. Nont of the other suggestins worked either.

Bernard Liengme said:
Try formatting the cell with the total with [hh]:mm:ss
Tell us if this works
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

womblew said:
Here is the set up.

Begin TimeEnd Time Total Time on call
7:00:00 17:15:00 10:15:00
8:00:00 14:00:00 6:00:00
8:30:00 17:00:00 8:30:00
7:15:00 16:20:00 9:05:00
9:55:00 16:30:00 6:35:00
7:00:00 9:00:00 2:00:00
8:15:00 9:35:00 1:20:00
7:00:00 8:15:00 1:15:00
13:00:00 14:30:00 1:30:00
13:30:00 14:50:00 1:20:00
6:30:00 16:00:00 9:30:00
8:15:00 15:50:00 7:35:00
10:00:00 14:30:00 4:30:00
8:00:00 11:00:00 3:00:00
7:00:00 13:00:00 6:00:00
8:00:00 16:00:00 8:00:00
8:00:00 13:00:00 5:00:00
13:00:00 16:00:00 3:00:00
8:00:00 12:00:00 4:00:00
14:00:00 15:00:00 1:00:00
12:30:00 16:45:00 4:15:00

I am trying to sum the total number of hours and minutes in Total time.
Begin and End Time are input as :mm/dd/yyyy hh:mm:ss AM/PM. Total Time is
formatted as hh:mm:ss. Begin Time is subtracted from End Time for Total
time
on call.

When I so auto sum for the Total Time at the bottom, I get 7:40:00. I know
there are more hours than 7. what am I doing wrong.
Click to expand...
Click to expand...
 
D

David Biddulph

The difference between 9103:40:00 and 103:40:40 is 375 whole days.

You say that your data were input as mm/dd/yyyy hh:mm:ss AM/PM so you need
to format the begin and end time cells that way so that you can see where
your extra days are hidden. Otherwise format your difference column (as
well as its total) as [h]:mm:ss, then you will see which row(s) have got the
extra days.
--
David Biddulph

womblew said:
This is not work It came back with "9103:40:00". I know that there were
not
9103 minutes there. Nont of the other suggestins worked either.

Bernard Liengme said:
Try formatting the cell with the total with [hh]:mm:ss
Tell us if this works
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

womblew said:
Here is the set up.

Begin TimeEnd Time Total Time on call
7:00:00 17:15:00 10:15:00
8:00:00 14:00:00 6:00:00
8:30:00 17:00:00 8:30:00
7:15:00 16:20:00 9:05:00
9:55:00 16:30:00 6:35:00
7:00:00 9:00:00 2:00:00
8:15:00 9:35:00 1:20:00
7:00:00 8:15:00 1:15:00
13:00:00 14:30:00 1:30:00
13:30:00 14:50:00 1:20:00
6:30:00 16:00:00 9:30:00
8:15:00 15:50:00 7:35:00
10:00:00 14:30:00 4:30:00
8:00:00 11:00:00 3:00:00
7:00:00 13:00:00 6:00:00
8:00:00 16:00:00 8:00:00
8:00:00 13:00:00 5:00:00
13:00:00 16:00:00 3:00:00
8:00:00 12:00:00 4:00:00
14:00:00 15:00:00 1:00:00
12:30:00 16:45:00 4:15:00

I am trying to sum the total number of hours and minutes in Total time.
Begin and End Time are input as :mm/dd/yyyy hh:mm:ss AM/PM. Total Time
is
formatted as hh:mm:ss. Begin Time is subtracted from End Time for Total
time
on call.

When I so auto sum for the Total Time at the bottom, I get 7:40:00. I
know
there are more hours than 7. what am I doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
S

Shane Devenshire

I ran with your numbers and it gave me 103:40 which looks correct. 9103:40
suggests that you are either formatting it incorrectly or that there are
dates in the cell instead of just times and one of those dates, at least is
not in the same day for both the start and end times. 9000 hours = 375 days
exactly.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


womblew said:
This is not work It came back with "9103:40:00". I know that there were not
9103 minutes there. Nont of the other suggestins worked either.

Bernard Liengme said:
Try formatting the cell with the total with [hh]:mm:ss
Tell us if this works
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

womblew said:
Here is the set up.

Begin TimeEnd Time Total Time on call
7:00:00 17:15:00 10:15:00
8:00:00 14:00:00 6:00:00
8:30:00 17:00:00 8:30:00
7:15:00 16:20:00 9:05:00
9:55:00 16:30:00 6:35:00
7:00:00 9:00:00 2:00:00
8:15:00 9:35:00 1:20:00
7:00:00 8:15:00 1:15:00
13:00:00 14:30:00 1:30:00
13:30:00 14:50:00 1:20:00
6:30:00 16:00:00 9:30:00
8:15:00 15:50:00 7:35:00
10:00:00 14:30:00 4:30:00
8:00:00 11:00:00 3:00:00
7:00:00 13:00:00 6:00:00
8:00:00 16:00:00 8:00:00
8:00:00 13:00:00 5:00:00
13:00:00 16:00:00 3:00:00
8:00:00 12:00:00 4:00:00
14:00:00 15:00:00 1:00:00
12:30:00 16:45:00 4:15:00

I am trying to sum the total number of hours and minutes in Total time.
Begin and End Time are input as :mm/dd/yyyy hh:mm:ss AM/PM. Total Time is
formatted as hh:mm:ss. Begin Time is subtracted from End Time for Total
time
on call.

When I so auto sum for the Total Time at the bottom, I get 7:40:00. I know
there are more hours than 7. what am I doing wrong.
Click to expand...
Click to expand...
Click to expand...
 
W

womblew

Thank you, I had a date conflict. one by a year and another by a few days.

Shane Devenshire said:
I ran with your numbers and it gave me 103:40 which looks correct. 9103:40
suggests that you are either formatting it incorrectly or that there are
dates in the cell instead of just times and one of those dates, at least is
not in the same day for both the start and end times. 9000 hours = 375 days
exactly.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


womblew said:
This is not work It came back with "9103:40:00". I know that there were not
9103 minutes there. Nont of the other suggestins worked either.

Bernard Liengme said:
Try formatting the cell with the total with [hh]:mm:ss
Tell us if this works
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Here is the set up.

Begin TimeEnd Time Total Time on call
7:00:00 17:15:00 10:15:00
8:00:00 14:00:00 6:00:00
8:30:00 17:00:00 8:30:00
7:15:00 16:20:00 9:05:00
9:55:00 16:30:00 6:35:00
7:00:00 9:00:00 2:00:00
8:15:00 9:35:00 1:20:00
7:00:00 8:15:00 1:15:00
13:00:00 14:30:00 1:30:00
13:30:00 14:50:00 1:20:00
6:30:00 16:00:00 9:30:00
8:15:00 15:50:00 7:35:00
10:00:00 14:30:00 4:30:00
8:00:00 11:00:00 3:00:00
7:00:00 13:00:00 6:00:00
8:00:00 16:00:00 8:00:00
8:00:00 13:00:00 5:00:00
13:00:00 16:00:00 3:00:00
8:00:00 12:00:00 4:00:00
14:00:00 15:00:00 1:00:00
12:30:00 16:45:00 4:15:00

I am trying to sum the total number of hours and minutes in Total time.
Begin and End Time are input as :mm/dd/yyyy hh:mm:ss AM/PM. Total Time is
formatted as hh:mm:ss. Begin Time is subtracted from End Time for Total
time
on call.

When I so auto sum for the Total Time at the bottom, I get 7:40:00. I know
there are more hours than 7. what am I doing wrong.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding rota 0
15 minute data to daily data 1
Problem formatting dates from CSV file 2
I need Time Range to return Value 4
Countifs with different restrictions 1
Conditional Time Calculation 10
Extracting just the time portion 6
Checking for date and time overlaps 0

Top