Making a schedule using excel. HELP
Posted by Eddie S. on July 30, 2001 9:09 AM
Recently I was asked by my boss to see if I could make a work schedule showing the employees time (10-5, 1-9:30, etc) their total hours and the payroll dollars used. I have done the total hours and payroll dollars ok, but I am having troubles getting Excel to convert the time slots into hours. Example: I want Excel to convert 10-5 into 6.5 hours. I know I will probably have to make the calculations on another page, but is this fomula possible? Basically I need a lot of help here. Thank you in advance!
Eddie
Posted by Cory on July 30, 2001 10:14 AM
There's a way to calculate the difference between time, but it can't be done with the format you're using ("10-5"). For excel to be able to do this, you need to be a little more specific...maybe "10a-5p"?
If you do use this format then you're going to have to translate it for excel on another sheet so excel knows what times it's working with. Ex: "10a-5p" goes in A1 on the schedule. Over on sheet2 that "10a-5p" gets translated into "10:00 AM" in cell A1 and "5:00 PM" in cell A2. Then, in cell A3 (where you want the answer) enter the following formula:
=24*(IF(A1>A2,A2+1-A1,A2-A1))
To finish it you can link that result to a cell on the schedule where you want it to show up...
Hope this at least gets you pointed in the right direction...
Cory
Posted by Cory on July 30, 2001 10:17 AM
I forgot to add that you can find more help with this subject here:
http://www.cpearson.com/excel/datearith.htm
Wonderful website chock full of Excel and VBA goodies!
Cory
