| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Time
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
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 -- Biff Microsoft Excel MVP "womblew" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
use custom format
[h]:mm On Jan 27, 12:31*am, womblew wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
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" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
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" wrote: 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" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
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" wrote: 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" wrote: 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" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Time
Thank you, I had a date conflict. one by a year and another by a few days.
"Shane Devenshire" wrote: 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" wrote: 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" wrote: 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" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| straight time, time and a half, and double time | Excel Discussion (Misc queries) | |||
| Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
| verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Linear Mode
