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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|