MILITARY time conversion

I need to enter a clock in and clock out time and then convert this to hours.

I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

But I keep getting the #NAME Error....

Can someone please help me out?

Originally Posted by Unregistered

I need to enter a clock in and clock out time and then convert this to hours.

I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

But I keep getting the #NAME Error....

Can someone please help me out?

please convert 20:25 and 16:50

all I can tell u is 20:25 = 8:25 pm and 16:50 = 4:50 pm..the military clock is simply a 24 hour clock with 24 hours vice having two sets of 12 hour time frames. 1pm is 1300 hours, 1 am is 0100 hours.

General hints:

Break the formula up into pieces and see if the pieces work,

QUOTIENT(B1,100) in one cell and
QUOTIENT(A1,100) in another cell

if that works, put them together and try
(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24

Continue on with other parts of the formula until you discover what is causing the error.

RB

I am having problems converting time to hours. I am looking for a way to convert it or to understand it in a more simple way. What I do when the
employee clock in I take the time against the audit report which has hours on it and convert the clock in time to hours.



Help,

[gubment_cheez]

Originally Posted by Unregistered

I need to enter a clock in and clock out time and then convert this to hours.

I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

But I keep getting the #NAME Error....

Can someone please help me out?

QUOTIENT

Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.


On the Tools menu, click Add-Ins.

1. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
2. If necessary, follow the instructions in the setup program.

in excel format your cell to military time you can put in the regular time ie 3:30 PM and it will convert it to military time

Originally Posted by Unregistered

I need to enter a clock in and clock out time and then convert this to hours.

I tried this formula =(QUOTIENT(B1,100)-QUOTIENT(A1,100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

But I keep getting the #NAME Error....

Can someone please help me out?

The information from the Cheez in Post #6 enables the use of the QUOTIENT function and is one way to solve the problem.

If you cannot or do not want to load the Analysis ToolPak, there is an alternative solution. The following formula produces the same results as the QUOTIENT formula, but uses Excel intrinsic functions only:

=(TRUNC(B1/100)-TRUNC(A1/100))/24+(MOD(B1,100)-MOD(A1,100))/1440+IF(B1<A1,1)

Don't forget to format the output cell(s) using a 24-hour time format, such as "13:30".