Time Formating and calculations

Pat Hunt

New Member
Joined
Jan 11, 2004
Messages
2
Hi everyone!

I'm trying to create a spreadsheet that creates a work schedule. Names are vertical down and days of week with clock in and clock out underneath are horizontal across.

I'd like to use time formating of 12:00 AM for both clock in and clock out as we are running a 24 hour operation with employees coming and going at all times. What I am struggling with is being able to quickly and easily enter times for multiple employees without having to type 1-2-:-0-0- -A-M. Any solution to this? I'd also like to total up the hours for the week and then mulitply the hours by the wage rate. I've never used time formatting before and any help would be greatly appreciated. I'm mildly dangerous with Excel - I understand a lot and use many different functions, but not much about some of the less commonly used functions. Any help is greatly appreciated!!!

Thanks,

Patt
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

listerheb

Active Member
Joined
Aug 22, 2003
Messages
262
Once you have formatted the cell you should only have to enter 12: and 12:00 pm will show up, however I have the same problem with my work, I am forever entering times. What I did you get around this was go to tools/autocorrect and make shortcuts. t81 is replaced by 20:15 and the cell shows 8:15 pm. I did this for all the start times that I use and have had no problems, other than the fact that I sometimes need to turn off auto correct while editing formulae.

Hope that helps you.
 

Pat Hunt

New Member
Joined
Jan 11, 2004
Messages
2
I'm not following the logic from Listerheb. . . Can you please explain how you enter times a little bit better? I'm not sure what t81 means and how that relates to 20:15 or 8:15.

I get the formulas . . . Thanks for the help!!
 

listerheb

Active Member
Joined
Aug 22, 2003
Messages
262
Sure. I went into Tools, then auto correct, then I told it to replace as I type. In the replace field I put t81 Because to me that means Time 8:15 then in the replace with field I put 20:15 because entering times in 24 hour format is easier and quicker for excel to understand. But the way the cell is formatted after I select a cell and type t81 and tab out of that cell it displays 8:15 pm

I did this with all the times I use in my job and now entering shift times is way faster.

I hope I have been more clear, but if not feel free to send me a PM
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,530
Members
425,480
Latest member
br400821

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