Convert hours and minutes to military time for billing

I need  convert theses time to military time and then rounded up or down to the nearest quarter hour for billing purpose. I usually  just estimate and round up and down as I see fit up or down.  I have calculated actual minutes.  In column E I need a formula that will Rounded up or down to the closest Quarter Hour for billing.  I may not have the best formula in Column D and E.  I manually rounded F and not sure if I have fairly rounded that column. I always go back and forth.  So now I trying to find a fair rounding factor in Military time and a formula to calculate the time. Column F is my end Goal. Then I can add up the column for Total Hours and used that amount to bill Clients.

       A            B                 C                  D               E                      F                                                     


Date Beg End Actual time Converted to Military Time Rounded up or down to the closest Quarter Hour Formula for Column D Formula for Column E
6/19/2014 8:21 AM 8:37 AM 0:16 0.266666667 0.25 C2-B2 (D2-INT(D2))*24
6/19/2014 8:52 AM 9:32 AM 0:40 0.666666667 0.75 C3-B3 (D3-INT(D3))*24
6/19/2014 10:11 AM 12:51 PM 2:40 2.666666667 2.75 c4-b4 (D4-INT(D4))*24
6/19/2014 2:29 PM 2:31 PM 0:02 0.033333333 0.25 c5-b5 (D5-INT(D5))*24
6/19/2014 2:45 PM 3:54 PM 1:09 1.15 0.25 c6-b6 (D6-INT(D6))*24
6/23/2014 8:43 AM 9:20 AM 0:37 0.616666667 0.75 c7-b7 (D7-INT(D7))*24
6/23/2014 9:30 AM 12:51 PM 3:21 3.35 3.50 c8-b8 (D8-INT(D8))*24
6/23/2014 3:30 PM 3:40 PM 0:10 0.166666667 0.25 c9-b9 (D9-INT(D9))*24
6/23/2014 3:45 PM 4:12 PM 0:27 0.45 0.50 c10-b10 (D10-INT(D10))*24
6/23/2014 4:19 PM 5:17 PM 0:58 0.966666667 1.00 c11-b11 (D11-INT(D11))*24
6/23/2014 6:22 PM 6:37 PM 0:15 0.25 0.25 c12-b12 (D12-INT(D12))*24
6/24/2014 7:45 AM 8:35 AM 0:50 0.833333333 0.75 c13-b13 (D13-INT(D13))*24
6/24/2014 8:44 AM 8:59 AM 0:15 0.25 0.25 c14-b14 (D14-INT(D14))*24
6/24/2014 9:25 AM 9:42 AM 0:17 0.283333333 0.25 c15-b15 (D15-INT(D15))*24
6/24/2014 12:10 PM 12:31 PM 0:21 0.35 0.50 c16-b16 (D16-INT(D16))*24

Answer
Answer

Hi,

First of all, the converted to military time in column E can be computed as:

=D2*24

Format this column as Number.  This can be used for the Roundup/Rounddown

=MROUND(E2,0.25)

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated September 25, 2019 Views 754 Applies to: