Converting time using IF statement

[dakashmir]

Hello All,
We use Avaya for our time tracking that records our technicians login/logoff time on the phone. I can get a report that shows their login/logoff punches as well as the total time for that day. My staff has a hard time figuring out 100'ths for minutes which is what Avaya records the time as. I'm trying to build a table in Excel that will take their total time (8:65) and convert it to quarter hours (8:45) so they can record it for pay. I was trying to use the =IF(Right(A1,2)="#",",") combined with If(AND) statements but not getting anywhere. I wanted to the user to be able to just copy a weeks worth of total time into column A to have the calculations done. I tried to use column B to convert the time into a two digit number then have column C convert that number to either 00, 15, 30, or 45.
Column B: =IF(RIGHT(A2,2)="<=24","0",IF(AND(A2>=25,A2<=49),"15",IF(AND(A2>=50,A2<=74),"30",IF(AND(A2>=75,A2<=99),"45"))))
Column C: =IF(B2<=24,"0",IF(AND(B2>=25,B2<=49),"15",IF(AND(B2>=50,B2<=74),"30",IF(AND(B2>=75,B2<=99),"45"))))

After I get this figure this one out I'll tackle trying to build a macro that will take the Avaya file and copy it into this file and email it to the technician daily. So if you also have an idea on how to do that I'm all ears!

Thanks,
Rick

 

[crazypabs]

hopefully this will set you in the right direction...
=ROUNDDOWN(8.65,0)+MROUND((8.65-ROUNDDOWN(8.65,0))/100*60,0.15)

replace the 8.65 with the cell you have the decimal hours in and it should point you in the right direction...

cheers

Matthew

 

[dakashmir]

Matthew you are DaMan!!!!

 

[dakashmir]

I thought it worked the first time I did the test, then when I built the table out it didn't. The problem I have is in your example you used 8.65 but the time uses 8:65 which gives me a result of 0.3. Back to the drawing board.