vlookup not working on calculated time
 

Hi,

I am trying to convert a time (in a timesheet record I keep) into a 'number of hours' on a daily basis, so that I can calculate the weekly total.

In order to do this, I have a table as follows:

Columns: Arrive*, Leave*, Total Hours(1)*, Total Hours(2)**

*expressed in Custom,hh:mm format, e.g. 07:30
**hopefully expressed as a decimal, e.g. 7.5

So I enter an arrive and leave time, have a simple subtract formula to calculate Total Hours(1) and use the following vlookup to populate Total Hours(2):

=IF(ISBLANK(G3),"0",VLOOKUP(H3,Config!$B$3:$C$97,2 ,0))

As you have probably guessed from the formula, I have a config sheet with a table that lists 00:00 - 23:45 in 15 min increments in column B, and 0 - 23.75 in 0.25 increments in column C.

I dare say that the whole thing is very ineloquent, so am happy to be advised an overall alternative solution, but PLEASE help me fix what I have first, just as it is bugging the bejeesers out of me!!

Many thanks in advance!!
Steve

vlookup not working on calculated time
 

Hi Steve,

Am Fri, 20 Sep 2013 09:24:26 +0100 schrieb aa_Steve:

Columns: Arrive*, Leave*, Total Hours(1)*, Total Hours(2)**

your Total Hours(1) in column F in F2
Then in G2:
=ROUNDUP(F2*96,0)/4


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus,

Thanks for the response, unfortunately that doesn't work. Perhaps you could ellaborate on what this is intended to do?

Steve

vlookup not working on calculated time
 

Hi,

Am Fri, 20 Sep 2013 12:22:52 +0100 schrieb aa_Steve:

Thanks for the response, unfortunately that doesn't work. Perhaps you
could ellaborate on what this is intended to do?

it rounds up the total time (hh:mm) to the füll quarter and the output
is decimal time.
Format the cell General


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

Sorted - thanks!

Steve