OnlineConversion Forums Welcome to OnlineConversion.com Forums

Go Back   OnlineConversion Forums > Main Forums > Convert and Calculate

Convert and Calculate Post any conversion related questions and discussions here. If you're having trouble converting something, this is where you should post.
* Guest Posting is allowed.

Reply
 
Thread Tools Display Modes
  #1  
Old 09-10-2007, 08:25 AM
Unregistered
Guest
 
Posts: n/a
Default 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?
Reply With Quote
  #2  
Old 10-30-2007, 11:38 AM
Unregistered
Guest
 
Posts: n/a
Default Re: MILITARY time conversion

Quote:
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
Reply With Quote
  #3  
Old 10-31-2007, 12:19 PM
Unregistered
Guest
 
Posts: n/a
Default Re: MILITARY time conversion

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.
Reply With Quote
  #4  
Old 11-01-2007, 01:03 PM
Unregistered
Guest
 
Posts: n/a
Default Re: MILITARY time conversion

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
Reply With Quote
  #5  
Old 12-17-2007, 03:21 PM
Unregistered
Guest
 
Posts: n/a
Default Re: MILITARY time conversion

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,
Reply With Quote
  #6  
Old 12-19-2007, 05:18 AM
gubment_cheez's Avatar
gubment_cheez gubment_cheez is offline
Ultimate Supreme Bacon Cheese Member
Long Time Member The Golden Calculator Award The Tree House Award Over 100 Posts
 
Join Date: Jan 2007
Location: Sac-o-Tomato, Cowafornia
Posts: 354
Rep Power: 8
gubment_cheez is on a distinguished road
Default Re: MILITARY time conversion

Quote:
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.
__________________
duct tape
duct tape
it's better than glue
fixes stuff
just like new
be prepared throughout your life
keep a roll beneath
your pillow at night
Reply With Quote
  #7  
Old 12-19-2007, 01:45 PM
Unregistered
Guest
 
Posts: n/a
Smile Re: MILITARY time conversion

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
Reply With Quote
  #8  
Old 12-19-2007, 08:51 PM
Roy Nakatsuka
Guest
 
Posts: n/a
Default Re: MILITARY time conversion

Quote:
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".
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 01:49 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 1997-2005, 2007, OnlineConversion.com