X value of cell has value of cell from colum

[AA Arens]

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

 

[T. Valko]

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

 

[AA Arens]

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

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

 

[Roger Govier]

Hi

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)

 

[T. Valko]

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