If function

[tommorgan]

Hello all,
I have a couple of problems putting together an accounts spread sheet.

The first is how do I insert a mask into a cell that will allow me to enter
four digits an automatic forward slash and four more digits and not allow
any variation on the total of eight digits, for example 2359/1589 or
0236/0480.

The second problem is creating a formula to go in the '14 Day action' column
that will enter 'Letter' when the entry in the '14 Day date' column is
greater than todays date, but will be over ridden by 'paid' if there is an
entry in the 'Paid Date' column.
Client name Matter Category Amount GST Fee Account date Paid Date 14 Day
14 Day
date action
Charlie Brown 2005/1966 LSA $1,254.23 $139.36 $1,114.87 03-Nov-05
17-Nov-05
Jo Bloggs 2005/2059 AP $236.25 $26.25 $210.00 02-Nov-05 16-Nov-05
Mary 2005/2541 PVT $125.89 $13.99 $111.90 01-Nov-05 15-Nov-05
hgdhjgh 2005/1232 PVT $1,145.56 $127.28 $1,018.28 11-Nov-05 20-Nov-05
25-Nov-05 LETTER
11-Nov-05 20-Nov-05 25-Nov-05 Paid

Thanks for any help,
Tom

 

[Roger Govier]

Hi Tom

For the first question, use Data>Validation>drop down select Custom and in
the white pane enter
=AND(ISNUMBER(--(LEFT(B2,4))),ISNUMBER(--(RIGHT(B2,4))),MID(B2,5,1)="/")
change cell reference to suit. Apply format to the range where data entry is
to take place.
On the Error Alert tab you could type in an example of the data entry expected.

For the second part, in cell I2 enter
=IF(G2<>"","Paid",IF(TODAY()>H2,"Letter",""))

Regards

Roger Govier

 

[damorrison]

format your cells,
highlight your cells you want to format,
goto-format-cells-number tab-custom,
in the custom box type "2359/"####
then enter, so whenever there is a number in that range the number
2359/ will always be in front of it,
but,
if you want a formula to have two cells together with the slash go like
this
=A1&"/"&B1
dave