X value of cell has value of cell from colum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 23 Jun 2007 07:40 GMT

I am busy with making a office present and absent sheet.

Arrival
07.00 07.15 07.30 etc
------------------------------------------
Jan 1 X
Jan 2
etc.

Same for departure

Employees has to put a cross (X) under the arrival time column at the
present date.

Then I want to calculate the working hours.

How to make a formula that takes the value from the column of the X
(arrival) minus the departure time?

Like 07.30 - 17.00 = .... hours.

Thanks a lot.

Bart, excel 2003

Reply to this Message

T. Valko - 23 Jun 2007 08:17 GMT

Try something like this:

Row 1 are the times
Row 2 = X

=IF(COUNTIF(B2:J2,"x")<2,"",INDEX(B1:J1,MATCH("xxxxx",B2:J2))-INDEX(B1:J1,MATCH("*",B2:J2,0)))

Format as h:mm

This does not account for times that might span past midnight. If this might
be possible then it gets much more complicated.

Biff

>I am busy with making a office present and absent sheet.
>
[quoted text clipped - 20 lines]
>
> Bart, excel 2003

Reply to this Message

AA Arens - 23 Jun 2007 09:49 GMT

> Try something like this:
>
[quoted text clipped - 18 lines]
> > Jan 2
> > etc.

Thanks Biff. This was the formula I used.

=IF(COUNTIF(Danis!D4:AC4,"x")<2,"",INDEX(Danis!N$3:AC
$3,MATCH("x",Danis!N4:AC4,0))-INDEX(Danis!D$3:L$3,MATCH("x",Danis!
D4:L4)))

In the colonm next to this, I want to have a value overtime,
indicating an overtime if the amount of working hours (which I get
from above formula) is >10.

I used the formula =IF(D4<10,"",D4-8), but I get no value if the cell
format is h:mm

How to get the value and how to set < or equal to 10?

Bart

> > Employees has to put a cross (X) under the arrival time column at the
> > present date.
[quoted text clipped - 9 lines]
>
> > Bart, excel 2003

Reply to this Message

Roger Govier - 23 Jun 2007 18:27 GMT

> I used the formula =IF(D4<10,"",D4-8), but I get no value if the cell
> format is h:mm
>
> How to get the value and how to set < or equal to 10?

Times are stored as fractions of a day, so to convert to decimal hours,
multiply by 24

=IF(D4*24<=10,"",D4-8/24)

Signature

Regards

Roger Govier

>> Try something like this:
>>
[quoted text clipped - 50 lines]
>>
>> > Bart, excel 2003

Reply to this Message

T. Valko - 23 Jun 2007 19:18 GMT

Or, you could modify the other formula like this:

=IF(COUNTIF(Danis!D4:AC4,"x")<2,"",(INDEX(Danis!N$3:AC
$3,MATCH("x",Danis!N4:AC4,0))-INDEX(Danis!D$3:L$3,MATCH("x",Danis!
D4:L4)))*24)

Format as GENERAL or NUMBER.

Then your other formula will work and both results will be in decimal
format.

Biff

> Hi
>
[quoted text clipped - 60 lines]
>>>
>>> > Bart, excel 2003

Reply to this Message