Time Formatting

I am fairly new to Excel and I'm working on creating an excel sheet that figures the minutes between clock in and clock out times.
For example, if I clock in at 9:00 AM and clock out at 12:30 PM, I would see hours and minutes 3:30
I have been able to do this, but I'm looking for a way to make that calculation work with or without having to put a space before the AM & PM. So that time entered as 9:00 AM or 9:00am or 9:00AM would all calculate correctly? I've searched everywhere and I just can not figure this out.

Once the employee enters their clock in and clock out times, there is a drop down box to show what the clock out was for ie lunch , break, end of day etc... Is there a way to have the number of minutes calculated automatically go into a certain cell depending on which option is chosen from that drop down ? I have columns for lunch, break, training etc.... For example, if I choose lunch from the drop down, then the number of minutes I was at lunch, will auto populate in the lunch column.

Excel Example.JPG

You could use data validation to force them to type it in properly. If they type 9:00AM (without the space) they'll get an annoying little message and will soon learn to do it properly.

I would suggest using "military time" and negate the need to add AM/PM to the end of it, but I know some people don't like that.

Failing either of those you'll probably have to do some hand holding by over complicating your formulas to account for it.

Attach a sample workbook and we can try and provide a workable solution to that, and the second part about adding the minutes to a specific column.

BSB

I definitely like the data validation idea Once that little ping sound pops up enough times, they'll figure it out.
I love this website. You guys are terrific !

I'm going with data validation. We are really busy working on the phones in my job. I'm just creating this as a simple, handy tool for my team members to keep track of log in/out phone times. The data validation can be really aggravating constantly popping up, but not so much that they will ditch the tool all together. That data validation will be perfectly annoying but not a deal breaker LOL I am terrible about not entering that space myself, so I will be the first to enter it correctly the first time to stop that ping.

As for the second part of your question. You could accomplish it by using an IF formula to check what's in the 'Code' column then putting the value under the appropriate heading.

Look at the attached mock up of your file where I've put the below formula in E2 then copied it right and down as required.

Formula:

=IF($D2=E$1,$C2,"")

BSB

Thank you so much for showing me how this can be done. I have taken on the project of teaching myself excel and I don't have to say how this has been such an enormous task. I have made a few changes to my little time tracker and so far, everything has worked out okay, except for the formula to populate the minutes into the correct category automatically like you have shown in your mock file. I have tried everything and spent so many hours on trying to get this to work in my sheet and I have hit a wall. I have attached a copy of the sheet that I'm working on. I'm trying to make it so that when I select a category, then the minutes that I spent from clock in to clock out, will automatically go into the correct place. You show how to do this in the mock file, but I just cannot seem to get that to work. If you have any suggestions on how I might be able to do this or maybe what I'm missing in the excel you sent, I would be forever grateful.
thank you again so much
Candy