MrExcel Publishing
Your One Stop for Excel Tips & Solutions

a hard one for me please help this site is a god send


Posted by tessa gazzard on November 12, 2001 3:38 PM

i have a coloum let say B1 with a start time of say 5:30 pm in the next coloum i have an end time of 6:30pm in the next two coloum i want to be able to have how many minutes online at peck time and how many minuite in the next coloum off peck if in the sceanerio peck time is 6 am till 6 pm this one has been troubling me for days now please help i got one coloum labled off peck min and another coloum labled peck timee


Posted by Barrie Davidson on November 12, 2001 6:32 PM

Tessa, I assume your start time is in cell B1 and your end time is in cell C1. To get your peak and off-peak times enter the following formulas:

in cell D1 (peak times)
=C1-B1-E1

in cell E1 (off-peak times)
=MIN(IF(6/24-B1<0,0,6/24-B1)+IF(C1-18/24<0,0,C1-18/24),C1-B1)

I think this will do the job for you.

Regards,
BarrieBarrie Davidson