Get time from populated cells

[~TaC~]

Hi,

New to the forum and novice in Excel.

I have a sheet where I can fill in the times that colleagues work each day, this varies from day to day.

7-8	8-9	9-10	10-11	11-12	12-1	1-2	2-3	3-4	4-5	Start	Finish
		*	*	*	*
				*	*	*	*

<tbody>
</tbody>

What I would like is for the start and finish times to be auto populated showing first row start=09:00 and finish=13:00, second row start=11 and finish=15:00

I can't upload a picture of what I have done but the cells don't fill with * they are colour filled depending on hours worked.

How is this possible, I was hoping to use formula but now wondering if I need to use macro.

 

[FDibbins]

If you want the actual time shown, either use real times in your headings, or something like this in helper columns (M and N) and then hide K and L...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Name

5-6

6-7

7-8

8-9

9-10

10-11

11-12

12-13

Total

Start

End

2​

aa

1​

1​

1​

3​

5-6

8-9

5:00​

9:00​

3​

bb

1​

1​

1​

3​

7-8

10-11

10:00​

11:00​

4​

cc

1​

1​

1​

1​

1​

1​

1​

7​

5-6

11-12

11:00​

12:00​

5​

dd

1​

1​

1​

1​

1​

1​

6​

5-6

10-11

10:00​

11:00​

M2=TIMEVALUE(LEFT(K2,FIND("-",K2)-1)&":00")
N2=TIMEVALUE(RIGHT(L2,FIND("-",L2)-1)&":00")

Of course, you could include all that into 1 formula for Start and 1 for End (or even combine them together), but then the formula becomes more complex...
Start=TIMEVALUE(LEFT(INDEX($B$1:$I$1,MATCH(TRUE,INDEX(($B2:$I2<>0),0),0)),FIND("-",INDEX($B$1:$I$1,MATCH(TRUE,INDEX(($B2:$I2<>0),0),0)))-1)&":00")
End =TIMEVALUE(RIGHT(INDEX($B$1:$H$1,MATCH(0,B2:H2,-1)),FIND("-",INDEX($B$1:$H$1,MATCH(0,B2:H2,-1)))-1)&":00")

Or all in 1 (monster)...
=TEXT(TIMEVALUE(LEFT(INDEX($B$1:$I$1,MATCH(TRUE,INDEX(($B2:$I2<>0),0),0)),FIND("-",INDEX($B$1:$I$1,MATCH(TRUE,INDEX(($B2:$I2<>0),0),0)))-1)&":00"),"h:mm")&"-"&TEXT(TIMEVALUE(RIGHT(INDEX($B$1:$H$1,MATCH(0,B2:H2,-1)),FIND("-",INDEX($B$1:$H$1,MATCH(0,B2:H2,-1)))-1)&":00"),"h:mm")

 

[FDibbins]

The results in post #7 are pulling exactly what is in the headings in that table - and there is no 5-9 or any other combos in the headings

 

[~TaC~]

That looks more like what I am after, need to go bed now but will get back to you tomorrow and let you know how I get on.

Thanks so much for your help.

 

[Tetra201]

What I'm looking to achieve is the example I posted

Try the following formulas:

Cell K2 =TEXT(MATCH(1,$B2:$I2,0)+LEFT($B$1,FIND("-",$B$1)-1)-1,"00.00")
Cell L2 =TEXT($J2+$K2,"00.00")

 

[~TaC~]

Tetra201

This works great and has pretty much solved my problem and answered another question

The start time shows exactly as needed and finish time displays some days correctly.

What I am getting now is,

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-1	Total	Start	Finish
2	AA	1	1	1	1					4	05.00	09.00
3	BB			1	1	1	1	1		5	07.00	12.00
4	CC	1	1	1	1	0.5	1	1	1	7.5	05.00	12.30
5	DD										#N/A	#N/A

<tbody>
</tbody>

Ok as you can see, on days less than 8 hours where there is no lunch break it works perfectly. When there are 8 hours or more I enter 0.5 count for a half hour unpaid break but still want the finish time in row 4 to be 13:00.

Also when no data is entered I am getting the #N/A showing.

Definitely on the right track though

 

[Tetra201]

... When there are 8 hours or more I enter 0.5 count for a half hour unpaid break but still want the finish time in row 4 to be 13:00.
Also when no data is entered I am getting the #N/A showing.

Here are modified formulas:

Cell K2 =IFERROR(TEXT(MATCH(1,$B2:$I2,0)+LEFT($B$1,FIND("-",$B$1)-1)-1,"00.00"),"")
Cell L2 =IFERROR(TEXT($J2+($J2>=7.5)*0.5+K2,"00.00"),"")

 

[~TaC~]

Tetra201 you are a legend.

Worked perfectly, this is part of an on-going project but I will take new questions into new threads so please keep an eye out for me.

I think you will definitely be able to help!

Thanks.

 

[Tetra201]

You are welcome.

 

[~TaC~]

Need help again,

I thought this was working perfectly until I enter more than one 0.5 in to the same row.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-1	Total	Start	Finish
2	AA	1	1	1	1	0.5	1	1	1	7.5	05.00	13.00
3	BB		1	1	1	1	1	1		6	06.00	12.00
4	CC	1	1	1	1	0.5	1	1		6.5	05.00	11.50
5	DD	0.5	1	1	1	0.5	1	1	1	7	05.00	12.00
6	EE	1	1	1	1	0.5	1	1	0.5	7	05.00	12.00

<tbody>
</tbody>

I have formulas already set that deduct half hour break from Total(J), so formulas:

Cell K2 =IFERROR(TEXT(MATCH(1,$B2:$I2,0)+LEFT($B$1,FIND("-",$B$1)-1)-1,"00.00"),"")
Cell L2 =IFERROR(TEXT($J2+($J2>=7.5)*0.5+K2,"00.00"),"")

works great in this instance. They also work great where no break is taken and less than 8 hours are worked(Row 3).

In Row 4 where we allow a 0.5 break the finish time shows as 11.50 not 12.00, I assume this is to do with the second formula =IFERROR(TEXT($J2+($J2>=7.5)*0.5+K2,"00.00"),"")
Row 5 should show start time as 05.30 and
Row 6 finish time as 12.30

Is there a formula for all rows in column K + L as work times always change, so show accurate start finish times and display the half hour as .30 not .50?

So that the above table would look like

	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-1	Total	Start	Finish
2	AA	1	1	1	1	0.5	1	1	1	7.5	05.00	13.00
3	BB		1	1	1	1	1	1		6	06.00	12.00
4	CC	1	1	1	1	0.5	1	1		6.5	05.00	11.30
5	DD	0.5	1	1	1	0.5	1	1	1	7	05.30	13.00
6	EE	1	1	1	1	0.5	1	1	0.5	7	05.00	12.30

<tbody>
</tbody>

Thanks

 

[Tetra201]

You keep adding and changing conditions, and the formulas keep getting patch-fixed.
Quite often, when all conditions are know from the onset, more efficient formulas can be developed.

Anyways, the formulas below produce output per your latest example:

=IFERROR(TEXT(DOLLARFR(MATCH(TRUE,INDEX($B2:$I2<>"",,),0)+LEFT($B$1,FIND("-",$B$1)-1)-INDEX($B2:$I2,MATCH(TRUE,INDEX($B2:$I2<>"",,),0)),60),"00.00"),"")
=IFERROR(TEXT(DOLLARFR($J2+($J2>=7)*0.5+DOLLARDE(K2,60),60),"00.00"),"")