Sales Contest Formulas Help Needed

[Vero]

Hello,

This is my first post on this board and I really need the help. We are currently running a sales contest based on how many hours and minutes our sales reps are spending on the phone. I was wondering if there was a way to devise a formula that would calculate the following based on a time.

Let's assume that Rep Jane Doe spend 4:15 (as in 4 hours and 15 minutes) on the phone today. She has earned 75 points for the day becausze she earned 20 points per hour for the first three hours and another 15 points (1 pt per minute for each minute over the first 3 hours)

I am currently doing this manually and was wondering if there might be a way to do the math with a formula...

Any help will be greatly appreciated!!!

Vero

 

[Vtikhon]

Try this formula: =IF(A1<=3/24,A1*24*20,3*20)+IF(A1<=3/24,0,(A1-3/24)*24*60)

 

[Vero]

It looks like it is working!!! Thanks so much!!! I am going to edit my spreadsheet with your formula! Yahoo!!!

 

[Vero]

Ok, I think there may be a snag... If I do 1:10 of tyalk time, tyhe formula gives me 23.333 pts instead of 30 as it should be... I am going to try and look at your formula to see if I can find out what the issue might be but I am probably not at that level of proficiency with Excel.

 

[Vtikhon]

1 Hour*20 + 10 min (1/6 hour)*20 = 23.333
How do you get 30?

 

[Vero]

Well, we are trying to convert time into points.

1 Hour*20 + 10 min (1/6 hour)*20 = 23.333
How do you get 30? 30 comes from 20pts for the first full hour + 10Pts (1Pt per minute beyond the hour)

Remember, the sales rep gets 20Pts for each full hour spent on the phone up to 3 hours for a max of 60Pts. If the rep spends 1h20 on the phone she would get 20+10Pts. If she spent 4h35mn, she would get a total of 155Pts (60+95P). I hope this helps...