help on this problem

[Fegal]

G'day all

Here is my problem,

When I enter L into B3 I want B3 to show a message.

ie B3 is blank, I enter L into B3, it the changes L to 0645 - 1515.
and it needs to be in time format so at the end of the roster i can add how many hours in total.

if that makes sense.

any help much appreciated!

Regards
james

 

[Fegal]

i can solve the circular reference by having 2 tables. 1 as an input table and the second as the finished product. messy but it would work.
A big problem is showing the 0645 - 1515 and having excel work that out as 8.5.

 

[Fegal]

is this not possible?

 

[Fegal]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NAME	Mon 8 April		Tue 9 April		Wed 10 April		Thu 11 April		Fri 12 April		Sat 13 April		Sun 14 April
3	l	e	15:15	d	17:00	s	17:30	l	18:30	e	15:15	e	15:15	e	15:15
4		e	15:15	d	17:00	s	17:30	l	18:30	e	15:15	d	17:00	d	17:00
5		e	15:15	d	17:00	s	17:30	l	18:30	e	15:15	s	17:30	s	17:30
6		e	15:15	d	17:00	s	17:30	l	18:30	e	15:15	l	18:30	l	18:30

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

ok here is what i have.
c3 e3 and g3 calculates its time from the value in the column before. somehow i need b3 d3 and f3 to = c3 e3 and g3 -8.5