Time Calculations

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
I have a problem trying to figure out how to calculate time intervals. My data is as follows, dd/hrs/mins shown as 1:22:24 (which is 1 day,22 hours and 24 minutes. I need to be able to add and subtract them properly with a result that will show the same as the data given. Please help ! :cry:
 

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
sbendbuckeye, Your a Life Saver and will be cutting my work time by tons, can t thank you enough. Works like a champ. One more question thought, the visual Basic Editor insert we did here, will this have to be set up on any PC for others to use to get the formula to work as well or once I have this file set up that wayc and send it to them, it is always that way. Thanks again for your h (y) (y) elp.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
sbendbuckeye

Hi Again, well I wish I was replying with better news but for some reason, it is not allowing me to enter the data in hte way the formula prints out the results so unable to neter new data. I tried entering it as example 02:23:23 and have it differentiate from 00:15:33. The first time I gave you is the amount of days,hours minutes it took for me to Dipatch the veh into my facility from the original report. The second time is the amt of time it took for the veh to actually arrive here after the dispatch. There are multiple stops for the veh and I will be entering 5 time entries for this spreasheet to report various things to me on a monthly basis. Sorry to be such a pain, I thought everything was going to work fine till Istarted entering the data. My original responce was using data I had already entered. Help Again ! :pray:
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Hello Freewish,

1. The vba code that we entered belongs to the Excel workbook which contains it. So if you email your Excel file to someone or copy it to another directory, the VBA code will be copied along with it.

2. Not sure what you mean by your last post. Can you respond with some actual examples including cells in which you are using the formula. The formula acutally returns a string so you may need to right justify it.
 

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
sbendbuckeye. Sorry for not getting back sooner but I haven t had any time to get back to this project, was working on something else. Anyway, below is what the spreadsheet will look like and how I would like this to function and it is still not calculating the hours per day correctly. See what you think. This track two entries divided by III and I would like to be able to take an average of multiple entries (approx 13) and the far right side of the columns. Thank you for takling the time to look at this for me Freewish

A # Days Report to Check out 06:06:13 III 04:14:46
A adds up (B,C,D,E,F,G)

B Report to dispatched 02:23:23 III 1:04:53

C Dispatched to check in 00:15:33 III 0:11:20

D Check in to Assigned 02:01:46 III 1:19:25
Report to Assigned(add B,C,D) 4:40:42 III 2:35:38

E Assigned to Completed 00:12:22 III 0:12:57

F Completed to Dispatched 01:09:06 III 1:16:58

Report to Dispatched Out 6:02:10 III 4:05:33
F adds up (B,C,D,E,F)

G Dispatched to Check out 0:04:03 III 0:09:13
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440

ADVERTISEMENT

Hello Freewish,

Sorry, I'm not following you too well right now. Below is one of the examples you posted in your last response:

B Report to dispatched 02:23:23 III 1:04:53

Does that mean that in Col B this is the value? And if so, what does the left side signify and what does the right side signify? Do I understand you correctly that the string is to be split around the III?

Could you post a couple of sample lines showing what your input data is and how it should look after processing? Sorry for the delay! Good Luck!
 

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
sbendbuckeye
Is it possible for me to send you the file so you can see how I have it set up. What I entered my last entry was two different sets of times in days/hours/minutes. The times were seperated so you could see them on each side of the III because of the way this enters once I submit it. When the times are adding up, the middle one which is hours, should not exceed 24 and if it does, another day should be added to the left number. Example, Rod D should add the times that are in B+C+D and when I do it manually, it totals 5D:16H:42M not the 4:40:42 it does now. There are 13 columns of time entries adding in various ways in each column and then averaging at the end of each row for a monthly average. Let me know if you would like me to send the spreadsheet as far as I have it built so far. Thank s again for all your help Freewish
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi

Could you not use true date and time values?

Here all are formatted as D:hh:ss

EDIT:

Apparently I cannot use HTML maker yet. I was just trying to show that if you have your data formatted as above you could just use + and - as usual.
 

Freewish

New Member
Joined
Jan 26, 2005
Messages
16
Time Calculator

Fairwinds I have tried every setting under the Time format area and none of them will seperate into Day/Hours/Minutes or DD/HH/SS but yes that would work. Normally you would see HH/MM/SS or date tracking but I can t find anything that will do what I need it to do aside from creating it thru Visual Basic Editor. Thank you
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Hello Freewish,

Just send it to my signature minus the spam stuff (should end up j2associates, etc). I may not be able to get to it until Monday but I'll try.
 

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Hello Freewish,

Could you resend your sheet? I can't find it anywhere. Please accept my apologies for losing track of it.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,575
Messages
5,765,188
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top