Time Formating and calculations

[Pat Hunt]

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

 

[listerheb]

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.

 

[Brian from Maui]

If you would consider using military time to enter the start and end times, I have a work around.

Book1
	D	E	F	G	H
1	8:00PM	2:00AM	6:00	10.00	$60.00
2			6.00	10.00	$60.00
Sheet1

Format F2 as a number

 

[Pat Hunt]

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]

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