RE: Data Format Help

If A1 contains date in numeric, use TEXT to convert it to be weekday in text

Try : =VLOOKUP(TEXT(A1,"ddd"),Lookup_range,n,0)

What exactly are you trying to do here?

Without seeing as sample of what you are working with, hard to give exact suggestions, but just because the cell saysw Monday, the underlying value is still a 5-digit value - formatting only changes the cosmetics of teh cell, not it's contents.

Try to upload a sample worksheet instead of image.

I think this will be far simpler if you can add some helper column in. It can probably be done without them, but the formulas will become extremely complicated. I added 1 helper top calc the time worked and another to ID the day...

	M	N	O	P	Q	R	S	T
2	Status	Date	Start Time	Finish Time	Day Start	Day Finish
3	P	Mon 19/10/2015	3:30	8:00	8:00	18:00	Mon	4:30
4	F	Mon 19/10/2015	8:00	16:36			Mon	8:36
5	S	Mon 19/10/2015	8:00	16:36			Mon	8:36
6	P	Mon 19/10/2015	9:00	17:00			Mon	8:00
7	F	Mon 19/10/2015	16:24	0:00			Mon	7:36
8	F	Mon 19/10/2015	16:24	0:00			Mon	7:36
9	C	Mon 19/10/2015	16:30	0:00			Mon	7:30
10	P	Mon 19/10/2015	22:30	3:30			Mon	5:00
11	P	Tue 20/10/2015	3:30	8:00			Tue	4:30
12	F	Tue 20/10/2015	8:00	16:36			Tue	8:36
13	S	Tue 20/10/2015	8:00	16:36			Tue	8:36
14	P	Tue 20/10/2015	9:00	17:00			Tue	8:00
15	F	Tue 20/10/2015	16:24	0:00			Tue	7:36

S3=TEXT(N3,"ddd")
T3=MOD(P3-O3,1)
copied down as needed.

then to start your calc...

	B	C	D	E	F	G	H	I	J
8		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Pub
9	C	0.31	0.00	0.00	0.00	0.00	0.00	0.00	0.00
10	P	0.73	0.52	0.00	0.00	0.00	0.00	0.00	0.00
11	S	0.36	0.36	0.00	0.00	0.00	0.00	0.00	0.00
12	F	0.99	0.68	0.00	0.00	0.00	0.00	0.00	0.00

C9=SUMIFS($T$3:$T$15,$M$3:$M$15,$B9,$S$3:$S$15,C$8)
copied down and across

Does FDibbins' solution in #7 work?

P/S: Never mind upper question.
So now you have other raising issue. I am working with it.

Day time, cell L3:

Please Login or Register  to view this content.

Night time, cell K3:

=($E3+($E3<$C3)*1-$C3)*24-L3