A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

military time calculations



 
 
Thread Tools Display Modes
  #1  
Old November 18th, 2009, 04:28 AM posted to microsoft.public.excel.misc
NedG
external usenet poster
 
Posts: 3
Default military time calculations

I am trying to write a program that tracks time entries in "Military time"
and then average multiple entries, that cover a 24 hour cylcle for three
shifts. I have 90% of the formulas in place but have run into a snag. I
need to figure per hour, the number of calls and the average time each took
from call in to completion. There will be some hours without any calls and
some with several. I need to examine each entry to see when the call came in
and when completed, then take the elapsed time and store in a separate cell
to be averaged together with other entires from the same hour. When I use
the IF Statement to examine the times entered, As in B27, I get a
#NAME error, but when I use B28 Excel returns true. Anytime I examine the
entry, "less than" it returns true, but when it is "greater than" excel
returns #NAME I can not use = to as most of the entries will have
variuos minutes. The times will be between say 07:00 to 08:00 to 09:00
etc. through out the 24 hour cycle. I have not progressed to the "count" of
these entries yet. Time is most important.
--
An Old Guy That''s Still Learning
  #2  
Old November 18th, 2009, 05:24 AM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default military time calculations

The conditions should be

B2TIMEVALUE("7:00")
B2(7/24)

B2TIMEVALUE("8:00")
B2(8/24)


If this post helps click Yes
---------------
Jacob Skaria


"NedG" wrote:

I am trying to write a program that tracks time entries in "Military time"
and then average multiple entries, that cover a 24 hour cylcle for three
shifts. I have 90% of the formulas in place but have run into a snag. I
need to figure per hour, the number of calls and the average time each took
from call in to completion. There will be some hours without any calls and
some with several. I need to examine each entry to see when the call came in
and when completed, then take the elapsed time and store in a separate cell
to be averaged together with other entires from the same hour. When I use
the IF Statement to examine the times entered, As in B27, I get a
#NAME error, but when I use B28 Excel returns true. Anytime I examine the
entry, "less than" it returns true, but when it is "greater than" excel
returns #NAME I can not use = to as most of the entries will have
variuos minutes. The times will be between say 07:00 to 08:00 to 09:00
etc. through out the 24 hour cycle. I have not progressed to the "count" of
these entries yet. Time is most important.
--
An Old Guy That''s Still Learning

  #3  
Old November 18th, 2009, 06:31 AM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default military time calculations

Tell us exactly what formula you've used that gives #NAME!.
There is nothing in B27 which will return #NAME!

Additionally, Jacob has pointed out that you can't test 07:00 against 7, as
Excel times are stored as proportions of a day, not as a number of hours.
--
David Biddulph

"NedG" wrote in message
...
I am trying to write a program that tracks time entries in "Military time"
and then average multiple entries, that cover a 24 hour cylcle for three
shifts. I have 90% of the formulas in place but have run into a snag. I
need to figure per hour, the number of calls and the average time each
took
from call in to completion. There will be some hours without any calls
and
some with several. I need to examine each entry to see when the call came
in
and when completed, then take the elapsed time and store in a separate
cell
to be averaged together with other entires from the same hour. When I
use
the IF Statement to examine the times entered, As in B27, I get a
#NAME error, but when I use B28 Excel returns true. Anytime I examine
the
entry, "less than" it returns true, but when it is "greater than" excel
returns #NAME I can not use = to as most of the entries will have
variuos minutes. The times will be between say 07:00 to 08:00 to 09:00
etc. through out the 24 hour cycle. I have not progressed to the "count"
of
these entries yet. Time is most important.
--
An Old Guy That''s Still Learning



 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.