Usage Log - Calculating Time Difference in Excel - Start time and end time

[statman78]

I have a usage log that some of our inspectors were using. I am trying to calculate the difference between the start time and end time yet it seems that the time has been entered manually via text field. Is there an easy way to go about figuring this out? I already tried several time functions provided with in excel yet none seem to work (probably due to formatting). Any help would be appreciated. If all else fails I can manually go through and figure out the time difference but who wants to do that

 

[Tim Folkerts]

You are right, the formatting is a pain! The time is entered as a text string that doesn't follow Excel's standard formats. I first tried solving this the hard way (parsing out the text string into separate chunks for hour, min & AM/PM; then putting it all back together in proper time format).

Then I found the easier method. The problem with the original formating was a missing space between the number and AM/PM. (And some of the times use a period instead of a colon). It was much easier to insert the space (and check for periods), and then use the TIMEVALUE function. The only other trick is watching for the end of the day (or the time would come out close to -23 hr instaed of 1 hr).

You might want to proofread the times. There are a few around that seem to be typed incorrectly. Just look for the elapsed times that are unusually long....


Tim F

P.S. I left in the "hard" way since I had already doen, it, but the "easy" way is certainly easier!

 

[statman78]

Thank you Tim! I am going to come up with a a little time clock that will monitor the usage rather than typing them in an Excel. If anyone has any ideas for such a thing please let me know.

 

[ScottK]

Check this out.

=MINUTE(Start time - End time) returns a minute value
I got it to work while fiddling around (see attachment)

Of course this only works for minutes... if you go beyond 59 it recycles.

But then you an add a colum with =HOUR(Start time - End time)

Maybe there's a more elegant way, but this would be my quick fix.

 

[Greg B]

I have my EXCEL guru working on the spreadsheet for you. He is AMAZING!!! His preliminary look backs up some of the previous posts. You must stipulate the way people enter the data (hh:mm) am etc. So the fields need to be formatted so they do not allow any other style. this is easy enough to do and after a while everyone will get the HINT. A style sheet/switchboard rather than a flat spreadsheet is often a better way to enter data as it gives a new record for each entry and translates the info to the sheet. You can then have print functions and graph buttons etc. I'll see what he comes up with. for interest sake the code should have been:
= "End time" minus "start time" and format result cell as hh:mm. This will give you both hour and minute. What happens if the times go over 00:01??? do you also need a date function.

be back soon

 

[Greg B]

OK. I'm back and Jeff my Excel Guru has revamped the form (see attached S/S)

The blue columns have simply been formatted so that in future anyone entering the incorrect time format will be greeted by a 'pop-up' telling them to try again in the correct format of hh:mm am/pm.
Jeff has simply copied your current data over *there are some errors such as people completing tasks before they started!!!
Jeff's second table (green) has a massive formula but it will convert any data in those columns to a time format...no matter what they do. This is good if you have old data and want to automate them.

Go with the Blue columns and train the guys to simply add the correct format

Enjoy and I'll thank my man Jeff!

 

[statman78]

Greg thank you so much. Also thank your buddy Jeff for me
I did not expect much of a response to my query. I did notice though that the AM/PM functionality is not present. Since there is only 16 days worth of data and 2 months of data needs to be entered what is the best way to make a little time clock as a small pop up or something in Excel or access. I am willing to to reenter all the data if there is a robust process. I will probably have to go back and read my visual basic book Thanks again!

 

[Greg B]

Greg thank you so much. Also thank your buddy Jeff for me
I did not expect much of a response to my query. I did notice though that the AM/PM functionality is not present. Since there is only 16 days worth of data and 2 months of data needs to be entered what is the best way to make a little time clock as a small pop up or something in Excel or access. I am willing to to reenter all the data if there is a robust process. I will probably have to go back and read my visual basic book Thanks again!

Statman: You are most welcome and I have already thanked Jeff (Just look at the code he wrote for the Green table. He amazes me with his skill and he is really quick. Jeff says that you don't have to reenter the data as he can juts be transposed straight into a new table. The green table actually will take all of the data that your guys have enterd with ALL the mistakes and recalculate it into normal time.
I have sent you a PM (top right hand side opf your screen). If you send me a PM with your email address I'll give you my mine so you can send the data and I'll ask Jeff to look at it for you. Why do you want the clock? There is one in the tool bar of your screen. The AM/PM funciton is just a matter of selecting formatting and selecting what time reference you want to show in the cell we selected hh:mm because it was easy. You can select hh:mm: am/pm but it requires an extra effort by the operators. I try and get everyone to enter military time to avoid confusion

 

[Jen Kirley]

I'm not worthy!

 

[Greg B]

I'm not worthy!

Jennifer,

This guy amazes me. I ask for something and he will find a way to do it. He is self taught and very pedantic (a perfectionist) which makes him so good. I can forgive him because his work is top shelf.