Time Calculations

[Freewish]

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 !

 

[Freewish]

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 elp.

 

[Freewish]

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

 

[sbendbuckeye]

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]

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]

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]

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]

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]

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]

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]

Hello Freewish,

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