Hi there,

I am using Excel 2003, and I have a master sheet that I use that holds
information of clients I look after.

What I do is highlight all of my master list, then do a DATA | SORT, and
then I select the column titled Next Clean Date, Excel will then sort the
list into customers who are due next to the top. Then I highlight the
portion of the list that are due, copy then paste to another sheet (which is
my run sheet for the day) I then print the run sheet and then work out when
the customers are due for cleaning next.

The frequency range varies, from 2 weekly to 24 weekly, then I will write
down on the printed sheet what the next clean date is, then on the excel
sheet I will then type in that next clean date. On another sheet again, is
invoices that I have setup with specific cells that point to certain cells on
the run sheet.

What I want to know is, would it be possible for Excel to add days
automatically to a given frequency code and then the actual next clean date
shows up on the invoice, as I type this in manually on the run sheet at the
moment? It is rather a pain in the proverbial this way.

For example:

If the code 2W (2 weekly) is typed or pasted into the cell it belongs to,
Excel then using a formula located somewhere sees this and auto adds 14 days
to the next clean date that shows up when I paste the info in on the run
sheet.

So if 6/9/2007 shows up in the next clean date cell, Excel adds 14 days and
that next date 20/9/2007 is what shows up on the invoice. The same goes for
other frequencies, 4W (4 weekly) 6W (6 weekly) etc. 28 days and 42 days
would then need to be added to the date.

Can Excel even do this?

Thankyou for any help, please ask more questions if you need any more
clarification.

Cheers.

You could use something like

=IF(MID(B2,2,1)="W",VALUE(LEFT(B2,1))*7+A2,A2)

where A2 has a date, and B2 has 2W 4W 6W, etc.

HTH,
Bernie
MS Excel MVP


"StoneCutter" wrote in message
...
Hi there,

I am using Excel 2003, and I have a master sheet that I use that holds
information of clients I look after.

What I do is highlight all of my master list, then do a DATA | SORT, and
then I select the column titled Next Clean Date, Excel will then sort the
list into customers who are due next to the top. Then I highlight the
portion of the list that are due, copy then paste to another sheet (which is
my run sheet for the day) I then print the run sheet and then work out when
the customers are due for cleaning next.

The frequency range varies, from 2 weekly to 24 weekly, then I will write
down on the printed sheet what the next clean date is, then on the excel
sheet I will then type in that next clean date. On another sheet again, is
invoices that I have setup with specific cells that point to certain cells on
the run sheet.

What I want to know is, would it be possible for Excel to add days
automatically to a given frequency code and then the actual next clean date
shows up on the invoice, as I type this in manually on the run sheet at the
moment? It is rather a pain in the proverbial this way.

For example:

If the code 2W (2 weekly) is typed or pasted into the cell it belongs to,
Excel then using a formula located somewhere sees this and auto adds 14 days
to the next clean date that shows up when I paste the info in on the run
sheet.

So if 6/9/2007 shows up in the next clean date cell, Excel adds 14 days and
that next date 20/9/2007 is what shows up on the invoice. The same goes for
other frequencies, 4W (4 weekly) 6W (6 weekly) etc. 28 days and 42 days
would then need to be added to the date.

Can Excel even do this?

Thankyou for any help, please ask more questions if you need any more
clarification.

Cheers.

Bernie, wouldn't a UDF like this work for StoneCutter if the last cleaned
date is one cell to the left? James

Function W(wks)
Application.Volatile
W = Application.Caller.Offset(0, -1) + (wks * 7)
End Function


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
You could use something like

=IF(MID(B2,2,1)="W",VALUE(LEFT(B2,1))*7+A2,A2)

where A2 has a date, and B2 has 2W 4W 6W, etc.

HTH,
Bernie
MS Excel MVP


"StoneCutter" wrote in message
...
Hi there,

I am using Excel 2003, and I have a master sheet that I use that holds
information of clients I look after.

What I do is highlight all of my master list, then do a DATA | SORT, and
then I select the column titled Next Clean Date, Excel will then sort the
list into customers who are due next to the top. Then I highlight the
portion of the list that are due, copy then paste to another sheet (which
is
my run sheet for the day) I then print the run sheet and then work out
when
the customers are due for cleaning next.

The frequency range varies, from 2 weekly to 24 weekly, then I will write
down on the printed sheet what the next clean date is, then on the excel
sheet I will then type in that next clean date. On another sheet again,
is
invoices that I have setup with specific cells that point to certain
cells on
the run sheet.

What I want to know is, would it be possible for Excel to add days
automatically to a given frequency code and then the actual next clean
date
shows up on the invoice, as I type this in manually on the run sheet at
the
moment? It is rather a pain in the proverbial this way.

For example:

If the code 2W (2 weekly) is typed or pasted into the cell it belongs to,
Excel then using a formula located somewhere sees this and auto adds 14
days
to the next clean date that shows up when I paste the info in on the run
sheet.

So if 6/9/2007 shows up in the next clean date cell, Excel adds 14 days
and
that next date 20/9/2007 is what shows up on the invoice. The same goes
for
other frequencies, 4W (4 weekly) 6W (6 weekly) etc. 28 days and 42 days
would then need to be added to the date.

Can Excel even do this?

Thankyou for any help, please ask more questions if you need any more
clarification.

Cheers.

Zone,

It would work, but would require a change in how the OP works. And it is generally better to use
worksheet functions rather than UDFs....

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message ...
Bernie, wouldn't a UDF like this work for StoneCutter if the last cleaned date is one cell to the
left? James

Function W(wks)
Application.Volatile
W = Application.Caller.Offset(0, -1) + (wks * 7)
End Function


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
You could use something like

=IF(MID(B2,2,1)="W",VALUE(LEFT(B2,1))*7+A2,A2)

where A2 has a date, and B2 has 2W 4W 6W, etc.

HTH,
Bernie
MS Excel MVP


"StoneCutter" wrote in message
...
Hi there,

I am using Excel 2003, and I have a master sheet that I use that holds
information of clients I look after.

What I do is highlight all of my master list, then do a DATA | SORT, and
then I select the column titled Next Clean Date, Excel will then sort the
list into customers who are due next to the top. Then I highlight the
portion of the list that are due, copy then paste to another sheet (which is
my run sheet for the day) I then print the run sheet and then work out when
the customers are due for cleaning next.

The frequency range varies, from 2 weekly to 24 weekly, then I will write
down on the printed sheet what the next clean date is, then on the excel
sheet I will then type in that next clean date. On another sheet again, is
invoices that I have setup with specific cells that point to certain cells on
the run sheet.

What I want to know is, would it be possible for Excel to add days
automatically to a given frequency code and then the actual next clean date
shows up on the invoice, as I type this in manually on the run sheet at the
moment? It is rather a pain in the proverbial this way.

For example:

If the code 2W (2 weekly) is typed or pasted into the cell it belongs to,
Excel then using a formula located somewhere sees this and auto adds 14 days
to the next clean date that shows up when I paste the info in on the run
sheet.

So if 6/9/2007 shows up in the next clean date cell, Excel adds 14 days and
that next date 20/9/2007 is what shows up on the invoice. The same goes for
other frequencies, 4W (4 weekly) 6W (6 weekly) etc. 28 days and 42 days
would then need to be added to the date.

Can Excel even do this?

Thankyou for any help, please ask more questions if you need any more
clarification.

Cheers.

Bernie,
I agree on both points. But since =W(2) is pretty close to the 2W the OP
originally posted, and since StoneCutter's post was the reason I started
down this road, I had to post it!
Regards, James
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Zone,

It would work, but would require a change in how the OP works. And it is
generally better to use worksheet functions rather than UDFs....

HTH,
Bernie
MS Excel MVP


"Zone" wrote in message
...
Bernie, wouldn't a UDF like this work for StoneCutter if the last cleaned
date is one cell to the left? James

Function W(wks)
Application.Volatile
W = Application.Caller.Offset(0, -1) + (wks * 7)
End Function


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
You could use something like

=IF(MID(B2,2,1)="W",VALUE(LEFT(B2,1))*7+A2,A2)

where A2 has a date, and B2 has 2W 4W 6W, etc.

HTH,
Bernie
MS Excel MVP


"StoneCutter" wrote in message
...
Hi there,

I am using Excel 2003, and I have a master sheet that I use that holds
information of clients I look after.

What I do is highlight all of my master list, then do a DATA | SORT,
and
then I select the column titled Next Clean Date, Excel will then sort
the
list into customers who are due next to the top. Then I highlight the
portion of the list that are due, copy then paste to another sheet
(which is
my run sheet for the day) I then print the run sheet and then work out
when
the customers are due for cleaning next.

The frequency range varies, from 2 weekly to 24 weekly, then I will
write
down on the printed sheet what the next clean date is, then on the
excel
sheet I will then type in that next clean date. On another sheet
again, is
invoices that I have setup with specific cells that point to certain
cells on
the run sheet.

What I want to know is, would it be possible for Excel to add days
automatically to a given frequency code and then the actual next clean
date
shows up on the invoice, as I type this in manually on the run sheet at
the
moment? It is rather a pain in the proverbial this way.

For example:

If the code 2W (2 weekly) is typed or pasted into the cell it belongs
to,
Excel then using a formula located somewhere sees this and auto adds 14
days
to the next clean date that shows up when I paste the info in on the
run
sheet.

So if 6/9/2007 shows up in the next clean date cell, Excel adds 14 days
and
that next date 20/9/2007 is what shows up on the invoice. The same
goes for
other frequencies, 4W (4 weekly) 6W (6 weekly) etc. 28 days and 42
days
would then need to be added to the date.

Can Excel even do this?

Thankyou for any help, please ask more questions if you need any more
clarification.

Cheers.