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 :nope:

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. :bonk: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!

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.

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.

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

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

Greg thank you so much. Also thank your buddy Jeff for me :) :applause:
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 thank you so much. Also thank your buddy Jeff for me :) :applause:
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

I'm not worthy! :magic:

I'm not worthy! :magic:
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.

Greg,

People are getting confused with military time. If you enter 11:45 PM as your start time and 12:15 AM as your end time then the duration is not calculated. Is there a way to make this work? Thank you and Jeff for all your help.:)

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.
I hate to say it, but the formula is wrong. :(

The problem is that time counts strangely. Normally, you count starting at 1, 2, 3, ... However, time counts starting 12, 1, 2, ... That means that 12:50 PM is less than 1:05 PM, but if you just treat it as numbers, you get the wrong result.

Basically, the formula treats 12:30 PM as one hour AFTER 11:30 PM, rather than 11 hours BEFORE 11:30 PM

Also, going from one day to the next is a problem. I had tried to address these in my "hard" worksheet for converting the times, but it was a royal pain. That was why I went to simply adding the space to the original data and using the TIMEVALUE function.


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

Enjoy and I'll thank my man Jeff!:D
The Blue columns still need one tweek, for going to the next day. Currently the formula is something like
=IF(J8="","",J8-I8)

It should be something like
=IF(J8="","", IF(J8-I8>0,J8-I8,(J8+1)-I8) )

When J8-I8 is less than zero, then J8 is the next day, and you need to add 1 (day) to make it later than I8.

Tim F

I hate to say it, but the formula is wrong. :(

The problem is that time counts strangely. Normally, you count starting at 1, 2, 3, ... However, time counts starting 12, 1, 2, ... That means that 12:50 PM is less than 1:05 PM, but if you just treat it as numbers, you get the wrong result.

Basically, the formula treats 12:30 PM as one hour AFTER 11:30 PM, rather than 11 hours BEFORE 11:30 PM

Also, going from one day to the next is a problem. I had tried to address these in my "hard" worksheet for converting the times, but it was a royal pain. That was why I went to simply adding the space to the original data and using the TIMEVALUE function.



The Blue columns still need one tweek, for going to the next day. Currently the formula is something like
=IF(J8="","",J8-I8)

It should be something like
=IF(J8="","", IF(J8-I8>0,J8-I8,(J8+1)-I8) )

When J8-I8 is less than zero, then J8 is the next day, and you need to add 1 (day) to make it later than I8.

Tim F

Tim,

Thanks, we did understand this and asked if they needed to addrees time over a day change (midnight) but got no response. We had the formula on standby which includes a date cell. We have a similar but larger sheet at work to calculate loading times and products over days. We enter start and end time along with tonnage and it gives us an hourly tonnage rate and time used. It is very efficient.

Greg,

People are getting confused with military time. If you enter 11:45 PM as your start time and 12:15 AM as your end time then the duration is not calculated. Is there a way to make this work? Thank you and Jeff for all your help.:)

Statman,

Just change the format of the cells to accomodate 12 hr time but you will then need to add a date to ensure that (as Tim correctly pointed out) a start time of 12:50 pm knows that the finish time of 1.30 a.m IS THE NEXT DAY!!
It seems we may have assumed your knoweldge was greater in Excel. Why don't you tell us EXACTLY what you want, how it is enetered, what it is used for etc and we will get back to you.

Greg,

I apologize for not communicating. The spreadsheet was made by an inspector and was handed over to me when things were not working properly and I was in the middle of 3 internal audits and one ISO audit last week. Hence the reason why I turned to the forums for some help. I should have carefully thought it through before posting the spreadsheet.

Hi: I am trying to create a timesheet for trauma statistics on how long it takes the surgeon to respond. I have a button that when the cell is activated and the button clicked, the current date/time is stamped in that cell. I need to calculate the difference between start date/time and end date/time. I understand how you do it if it is only time, but this is a mixed date/time cell, i.e.NOW().

Date/Time calculations work fine in Excel. I've attached a quick example.

I have been doing a lot with time and date calculations lately. So I added some more in case these were useful.

CW

Hi CW: Thanks for the examples. I will take a look and see if these help me out.

I appreciate your help.

Bobbie Pilkington:bigwave: