MrExcel Publishing
Your One Stop for Excel Tips & Solutions

schedule and hours


Posted by Dale on March 01, 2001 12:39 AM

I have two columns with a start time and end time. I can subtract th two and get answer, how ever I need a formula that will give the answer in the form a whole number and fraction (i.e. 8 1/2). Also if the answer is more than 6 hours it needs to subtract 1 hour for lunch. Thanks for any help


Posted by David Hawley on March 01, 2001 1:10 AM


Hi Dale

If your start time is in cell A1 and your end time is in Cell B1, then in C1 enter:
=IF((B1-A1)*24>6,(B1-A1)*24-1,(B1-A1)*24)

Then format the cell as Fraction-Up to one digit(1/4)

Dave

OzGrid Business Applications

Posted by Mark W. on March 01, 2001 7:39 AM

Simpler...

=(B1-A1)*24-((B1-A1)*24>6)