MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!


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:


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...


Posted by Cory on July 30, 2001 10:17 AM

I forgot to add that you can find more help with this subject here:

Wonderful website chock full of Excel and VBA goodies!