HELP .. Need Time Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have named an area called employees. This is what the area looks like

A B
1 102 1.5 Hrs
2 105 2 hrs
3 108 1 hrs

In a different part of my excel sheet I am trying to find a formula will
look in d5 and if A1 from above appears it will add that time, plus the time
in the row above. Example

D E F
52 8:30 am
53 105 10:30 am
54 108 11:30 am
55 102 1:00 pm

So in other words, I entered a start time of 8:30 am. In F53, the formula
look at D53 and since 105 is 2 hours in the named area, the formula added 2
hours to the time from the cell above.

I hope this make sense. And I really could use this help ASAP.
 
Trevor,

I am having a hard time coming up with exactly the number to put in A1:B7 so
the time that calculates in F53 in some cases, is 1:05 hours later then the
time in F52. Do you know number I can put in. Here is an example of what I
have in A1:B7 now -
1 102 1.50 (adds 1-1/2 hours to time)
2 105 2.0 (adds 2 hours to time)
3 108 1.0 (adds 1 hour to time)
4 110 1.25 (adds 1:15 hours to time)
5 109 1.05 ........... this the number I need help
with. It just add 3 minutes to the time. I have tried using various numbers
and it is not consistant.
 
Cathy

$A1:$C6 column C for reference only

102 1.5 1:30:00 =1.5/24 formatted as hh:mm:ss
105 2 2:00:00
108 1 1:00:00
110 1.25 1:15:00
109 1.05 1:03:00
111 1:05 0:02:42 give or take ;-)

In cell D52: 109
In cell F52: 01:00:00 (one hour)
In cell F53: =F52+VLOOKUP(D53,$A$1:$B$7,2,FALSE)/24
formatted as hh:mm:ss
= 02:03:00
Note that 01:00:00 multiplied by 24 = 24:00:00 formatted as [hh]:mm:ss, that
is, 1 day. If formatted as general, it would display as 1 (number one)

Also note that 1:05 is different to 1.05. The first would be interpreted by
Excel as 1 hour and 5 minutes; the second simply as 1.05. So, if you
started with 1:05 rather than 1.05 you'd get a different result to what you
expect ... possibly the result you *are* getting.

I think, if you have 1:05 against the 109 entry, you're actually adding 2
minutes and 42 seconds to the original value ... maybe that's getting
rounded up ?

Hope this helps pinpoint the problem

Regards

Trevor
 
Back
Top