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]

Hi, welcome to the board

If those are cells with a number and anything else in them (a minus sign or minus and another number), then they are no longer values, they are text, and you cannot (simply) run calcs on text.

Also, if you are working with time, then it is always best if you use actual time entries...7:00 and 19:00 or 07:00 AM and 7:00 PM rather than just 7

 

[~TaC~]

Thanks for the welcome,

I'm assuming I can't upload an image to show what I'm doing so I will try to describe better.

Say row C has cells filled 5-6, 6-7, 7-8, 8-9, 9-10, 10,11, 11-12, 12-1.... 9-10, 10-11, representing 5am to 11pm, say column C to T.
Row D, E, F etc will have the hours staff have worked, these are colour filled to show at work.
At the end of each staff hours, column U and V. I have columns labelled Start, Finish.
I would like to auto populate the start and finish time in to these columns.

Does this make better sense of my issue? I have no idea how to work with cells formatted as time.

Thanks for your quick reply Ford

Regards
Tez

 

[FDibbins]

Sorry to be a downer (again)

Colour is cosmetic formatting, not data, and formulas work on data
Is there some other way that you could indicate worked for the period - like with an X or a 1? we could count the X's...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Name	5-6	6-7	7-8	8-9	9-10	10-11	11-12	12-13	Total
2​	aa	X	X	X	X					4​
3​	bb			1​		1​		1​	1​	4​
4​	cc	1​	1​	1​	1​	1​	1​	1​	1​	8​
5​	dd	X	X		X	X	X		X	6​

J2=COUNTA(B2:I2)
copied down

 

[~TaC~]

Your not being a downer lol, took me ages to work out what I did.

I forgot to say that I used conditional formatting to colour cells filled with "1", so they do have a value. I have used COUNTA to count number of hours worked.

What I am looking to do is fill other columns with the actual start finish times. So if they start at 7am and finish at 3pm, have 07:00 in one column and 15:00 in another.

In essence, I have done what you have displayed but then I want column K+L which would be start+finish.

 

[FDibbins]

OK, sounds do-able. Can I assume there will be no gaps btw start and end (unlike my sample)?

 

[FDibbins]

This should work, even if there are gaps...

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-13

Total

Start

End

2​

aa

1​

1​

1​

3​

5-6

8-9

3​

bb

1​

1​

1​

3​

7-8

10-11

4​

cc

1​

1​

1​

1​

1​

1​

1​

7​

5-6

11-12

5​

dd

1​

1​

1​

1​

1​

1​

6​

5-6

10-11

K2=INDEX($B$1:$I$1,MATCH(TRUE,INDEX(($B2:$I2<>0),0),0))
L2=INDEX($B$1:$H$1,MATCH(0,B2:H2,-1))
both copied down

 

[~TaC~]

	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	08.00	12.00
3	bb						1	1	1	3	10.00	13.00
4	cc	1	1	1	1	1				5	05.00	10.00
5	dd			1	1	1				3	07.00	10.00

<tbody>
</tbody>

 

[~TaC~]

Sorry I missed your reply, will give it a try and get back to you.

Thanks

 

[~TaC~]

Looking at your formulas, this wouldn't work.

If B2+C2 are populated then K2 should show 5-7
L3 should be 9-11 and so on

What I'm looking to achieve is the example I posted, I think I may need to get use to using time format?