Need help creating a formula

D

DonV

Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.

Or would a macro be better suited to handle this type of calculation?

Any examples of a formula or macro would be appreciated. BTW, I agreed to assist but have had little experience with this type of calculation.

DonV-
 
M

Mark Ivey

See if this formula will work for your needs:


=IF(TEXT(A1+21,"ddd")="Fri",A1+21,IF(TEXT(A1+22,"ddd")="Fri",A1+22,IF(TEXT(A1+23,"ddd")="Fri",A1+23,IF(TEXT(A1+24,"ddd")="Fri",A1+24,IF(TEXT(A1+25,"ddd")="Fri",A1+25,IF(TEXT(A1+26,"ddd")="Fri",A1+26,A1+27))))))


Change the A1 reference to fit your needs.

Mark Ivey
Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.

Or would a macro be better suited to handle this type of calculation?

Any examples of a formula or macro would be appreciated. BTW, I agreed to assist but have had little experience with this type of calculation.

DonV-
 
R

Ron Rosenfeld

Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.
Click to expand...


With your close date in a1:

=WORKDAY(A1,21)+7-WEEKDAY(WORKDAY(A1,21)-6)

If the 21 days ends on a Friday, this will return that Friday. Is that what
you want?

If the WORKDAY function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron
 
D

DonV

This is close but it returns a date that is only 17 or 19 days after the close date. It must be no less than 21 days. What do I need to change to make this work?
See if this formula will work for your needs:


=IF(TEXT(A1+21,"ddd")="Fri",A1+21,IF(TEXT(A1+22,"ddd")="Fri",A1+22,IF(TEXT(A1+23,"ddd")="Fri",A1+23,IF(TEXT(A1+24,"ddd")="Fri",A1+24,IF(TEXT(A1+25,"ddd")="Fri",A1+25,IF(TEXT(A1+26,"ddd")="Fri",A1+26,A1+27))))))


Change the A1 reference to fit your needs.

Mark Ivey
Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.

Or would a macro be better suited to handle this type of calculation?

Any examples of a formula or macro would be appreciated. BTW, I agreed to assist but have had little experience with this type of calculation.

DonV-
 
M

Mark Ivey

It seems to work fine on my end. I calculated it for up to 10 years worth of dates. The minimum I got for this period was 21 days and the maximum I got was 27 days. This should meet your requirements. Please let me know what errors you ran across so I can try to fix it.

Here is an example of some of the data I was calculating...
Original Date New Date DateDif
12/12/2007 1/4/2008 23
12/13/2007 1/4/2008 22
12/14/2007 1/4/2008 21
12/15/2007 1/11/2008 27
12/16/2007 1/11/2008 26
12/17/2007 1/11/2008 25
12/18/2007 1/11/2008 24
12/19/2007 1/11/2008 23
12/20/2007 1/11/2008 22
12/21/2007 1/11/2008 21
12/22/2007 1/18/2008 27
12/23/2007 1/18/2008 26
12/24/2007 1/18/2008 25
12/25/2007 1/18/2008 24
12/26/2007 1/18/2008 23
12/27/2007 1/18/2008 22
12/28/2007 1/18/2008 21
12/29/2007 1/25/2008 27
12/30/2007 1/25/2008 26
12/31/2007 1/25/2008 25
1/1/2008 1/25/2008 24
1/2/2008 1/25/2008 23
1/3/2008 1/25/2008 22
1/4/2008 1/25/2008 21


Mark Ivey
This is close but it returns a date that is only 17 or 19 days after the close date. It must be no less than 21 days. What do I need to change to make this work?
See if this formula will work for your needs:


=IF(TEXT(A1+21,"ddd")="Fri",A1+21,IF(TEXT(A1+22,"ddd")="Fri",A1+22,IF(TEXT(A1+23,"ddd")="Fri",A1+23,IF(TEXT(A1+24,"ddd")="Fri",A1+24,IF(TEXT(A1+25,"ddd")="Fri",A1+25,IF(TEXT(A1+26,"ddd")="Fri",A1+26,A1+27))))))


Change the A1 reference to fit your needs.

Mark Ivey
Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.

Or would a macro be better suited to handle this type of calculation?

Any examples of a formula or macro would be appreciated. BTW, I agreed to assist but have had little experience with this type of calculation.

DonV-
 
M

Mark Ivey

I see your requirements now...

Overlook my last post. I see it deals with BUSINESS days not just number of days.

Ron's latest post should take care of your requirements!!!!!
It seems to work fine on my end. I calculated it for up to 10 years worth of dates. The minimum I got for this period was 21 days and the maximum I got was 27 days. This should meet your requirements. Please let me know what errors you ran across so I can try to fix it.

Here is an example of some of the data I was calculating...
Original Date New Date DateDif
12/12/2007 1/4/2008 23
12/13/2007 1/4/2008 22
12/14/2007 1/4/2008 21
12/15/2007 1/11/2008 27
12/16/2007 1/11/2008 26
12/17/2007 1/11/2008 25
12/18/2007 1/11/2008 24
12/19/2007 1/11/2008 23
12/20/2007 1/11/2008 22
12/21/2007 1/11/2008 21
12/22/2007 1/18/2008 27
12/23/2007 1/18/2008 26
12/24/2007 1/18/2008 25
12/25/2007 1/18/2008 24
12/26/2007 1/18/2008 23
12/27/2007 1/18/2008 22
12/28/2007 1/18/2008 21
12/29/2007 1/25/2008 27
12/30/2007 1/25/2008 26
12/31/2007 1/25/2008 25
1/1/2008 1/25/2008 24
1/2/2008 1/25/2008 23
1/3/2008 1/25/2008 22
1/4/2008 1/25/2008 21


Mark Ivey
This is close but it returns a date that is only 17 or 19 days after the close date. It must be no less than 21 days. What do I need to change to make this work?
See if this formula will work for your needs:


=IF(TEXT(A1+21,"ddd")="Fri",A1+21,IF(TEXT(A1+22,"ddd")="Fri",A1+22,IF(TEXT(A1+23,"ddd")="Fri",A1+23,IF(TEXT(A1+24,"ddd")="Fri",A1+24,IF(TEXT(A1+25,"ddd")="Fri",A1+25,IF(TEXT(A1+26,"ddd")="Fri",A1+26,A1+27))))))


Change the A1 reference to fit your needs.

Mark Ivey
Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.

Or would a macro be better suited to handle this type of calculation?

Any examples of a formula or macro would be appreciated. BTW, I agreed to assist but have had little experience with this type of calculation.

DonV-
 
D

DonV

Yes Ron's formula works exactly as required. Thanks to you both for the effort. I have one more small problem. They want to be able to apply a letter code to an amount paid.

Example: if the amount is 20.00 this would = 'D', if 30.00 'L', if 40.00 'S', if 50.00 'P'. What this is for I don't know but I looked in the help for a simple answer but did not find anything that would apply it on an IF-Then-Else type of formula.

Any ideas how I can make that happen?
I see your requirements now...

Overlook my last post. I see it deals with BUSINESS days not just number of days.

Ron's latest post should take care of your requirements!!!!!
It seems to work fine on my end. I calculated it for up to 10 years worth of dates. The minimum I got for this period was 21 days and the maximum I got was 27 days. This should meet your requirements. Please let me know what errors you ran across so I can try to fix it.

Here is an example of some of the data I was calculating...
Original Date New Date DateDif
12/12/2007 1/4/2008 23
12/13/2007 1/4/2008 22
12/14/2007 1/4/2008 21
12/15/2007 1/11/2008 27
12/16/2007 1/11/2008 26
12/17/2007 1/11/2008 25
12/18/2007 1/11/2008 24
12/19/2007 1/11/2008 23
12/20/2007 1/11/2008 22
12/21/2007 1/11/2008 21
12/22/2007 1/18/2008 27
12/23/2007 1/18/2008 26
12/24/2007 1/18/2008 25
12/25/2007 1/18/2008 24
12/26/2007 1/18/2008 23
12/27/2007 1/18/2008 22
12/28/2007 1/18/2008 21
12/29/2007 1/25/2008 27
12/30/2007 1/25/2008 26
12/31/2007 1/25/2008 25
1/1/2008 1/25/2008 24
1/2/2008 1/25/2008 23
1/3/2008 1/25/2008 22
1/4/2008 1/25/2008 21


Mark Ivey
This is close but it returns a date that is only 17 or 19 days after the close date. It must be no less than 21 days. What do I need to change to make this work?
See if this formula will work for your needs:


=IF(TEXT(A1+21,"ddd")="Fri",A1+21,IF(TEXT(A1+22,"ddd")="Fri",A1+22,IF(TEXT(A1+23,"ddd")="Fri",A1+23,IF(TEXT(A1+24,"ddd")="Fri",A1+24,IF(TEXT(A1+25,"ddd")="Fri",A1+25,IF(TEXT(A1+26,"ddd")="Fri",A1+26,A1+27))))))


Change the A1 reference to fit your needs.

Mark Ivey
Hi,
I need to create a formula that will calculate a date 21 business days from a date entered into an adjacent cell.

Example:
In cell A a date would be entered say, 12/12/07,
Cell B would then insert a date that is 21 business days later, but also add 1 day to the nearest Friday. So 21 days after 12/12/07 would be 1/10/08 and the nearest Friday would be 1/11/08. This would make it actually 22 days.

If the date enter is say 12/17/07, 21 days after would be 1/14/08 plus 4 days to the nearest Friday would actually make it 25 days.
I realize this sounds complicated but they need it to do this based on the date entered for calculating paydays for flat rate work.

This may explain what they are trying to do; "Every Friday, 21 days after a service call is closed, we mail a check to the technician."

So, if 21 days after a service call is closed ends on a Monday for example, the formula would need to add 4 more day to the 21 days. Or would it be easier for them to start from the Friday following the close date? So say the close date is 12/12/07, they would enter 12/14/07 (which is the Friday following the close date). The formula would then insert a date 21 days following Friday 12/14/07 which would simplify the formula calculation, I think.

Or would a macro be better suited to handle this type of calculation?

Any examples of a formula or macro would be appreciated. BTW, I agreed to assist but have had little experience with this type of calculation.

DonV-
 
R

Ron Rosenfeld

This one seems to be what is required. I will do more testing to see.
Click to expand...

Let me know if you run into any difficulties.

With regard to your other issue, of appending a letter to the amount paid, two
questions:

1. Does the amount still need to be treated as a number?
2. Is it the case that payments will be only 20, 30, 40 or 50; or could they
be some other amount? And, if so, should there be any letter suffix or no
letter suffix?
--ron
 
D

DonV

I don't know if the values will ever change, they just asked how to do it.
They will input dollar amounts like $20.00 and that would equal "D". I'm not
sure how a letter suffix would apply to 20, 30 40 or 50 dollars.
 
R

Ron Rosenfeld

I don't know if the values will ever change, they just asked how to do it.
They will input dollar amounts like $20.00 and that would equal "D". I'm not
sure how a letter suffix would apply to 20, 30 40 or 50 dollars.
Click to expand...

It is still not clear to me what it is that you want to do, or whether you
still require these values to be treated as numbers.

The best solution will depend on the answers to these and my previous
questions, so you will have to get "them" to define their requirements more
completely.
--ron
 
D

DonV

I would suppose the values can be treated as numbers as long as the solution
returns a letter for the value. The number/dollar amount will be entered in
one cell and the letter will be applied in an adjacent cell. This I am told
is for some sort of internal tracking.

So in cell A1 for example, the value would be input as $20.00 or 20
(whichever is applicable) and cell B1 would return D, if the value is
changed to say $40.00 or 40 (whichever is applicable) B1 would then return S
 
R

Ron Rosenfeld

I would suppose the values can be treated as numbers as long as the solution
returns a letter for the value. The number/dollar amount will be entered in
one cell and the letter will be applied in an adjacent cell. This I am told
is for some sort of internal tracking.

So in cell A1 for example, the value would be input as $20.00 or 20
(whichever is applicable) and cell B1 would return D, if the value is
changed to say $40.00 or 40 (whichever is applicable) B1 would then return S
Click to expand...

That's actually pretty simple. You can just use a lookup table.

Set up a table:

20 D
30 L
40 S
50 P


Then use the formula:

=VLOOKUP(cell_ref,Tbl,2,0)

where cell_ref refers to the cell containing the 20, 40, etc.

As written, the formula will return an error if the value is not listed exactly
in column 1. Again, that can be changed depending on specifications.
--ron
 
D

DonV

Hi Ron, thanks for your help. I don't think that VLOOKUP is what they need
because it only compares a single value (20) for example, and returns a
result based on that value. If the value in the next row is different the
return is #N/A.

What they need is to be able to compare the value in a range of cells and
return an associated letter. So, given the values 20,30,40 and the letters
D, S, L. If 20 is entered in cell A1, cell B1 would return the letter D.
However if cell A1 is later changed to 40 then cell B1 would change to the
letter L and so on. Now imagine a list of jobs, say A1 to A5. Each job might
have a different value. Depending on the value in each cell, B1 to B5 would
return an associated letter.
I hope I'm making sense. Sorry for the confusion.
 
R

Ron Rosenfeld

Hi Ron, thanks for your help. I don't think that VLOOKUP is what they need
because it only compares a single value (20) for example, and returns a
result based on that value. If the value in the next row is different the
return is #N/A.

What they need is to be able to compare the value in a range of cells and
return an associated letter. So, given the values 20,30,40 and the letters
D, S, L. If 20 is entered in cell A1, cell B1 would return the letter D.
However if cell A1 is later changed to 40 then cell B1 would change to the
letter L and so on. Now imagine a list of jobs, say A1 to A5. Each job might
have a different value. Depending on the value in each cell, B1 to B5 would
return an associated letter.
I hope I'm making sense. Sorry for the confusion.
Click to expand...

Either I'm not understanding you, or you don't understand VLOOKUP, or you
didn't implement it as I thought you would

Please copy exactly, and post them here, the formulas that you placed in B1:B5.
Also the Table and the location (cell address) of the Table.
--ron
 
D

DonV

You are correct, I don't understand VLOOKUP. What I read in the help file
leads me to understand that VLOOKUP can act on one row only, they need a
formula that will act on any row and return the correct letter, regardless
of the value they enter in a cell.

In the formula =VLOOKUP(20,A2:B4,2,0), will look for all values of 20, if
there is a match then the letter D is returned. If the cell has a different
value #N/A will be returned.

The table is
20.00 D D
30.00 S #N/A
40.00 L #N/A


In order to get the value of the next row the VLOOKUP would need to have the
next rows value in it such as =VLOOKUP(30,A2:B4,2,0).
That is the same as manually entering the letter. They want a formula that
will do this based on the value (20,30,40) entered, automatically. So it
does not matter what value they put in a cell as long as it is an amount
that has a letter associated with it. In the example above, if I change
20.00 to 30.00 the VLOOKUP will return #N/A instead of S.
 
R

Ron Rosenfeld

You are correct, I don't understand VLOOKUP. What I read in the help file
leads me to understand that VLOOKUP can act on one row only, they need a
formula that will act on any row and return the correct letter, regardless
of the value they enter in a cell.

In the formula =VLOOKUP(20,A2:B4,2,0), will look for all values of 20, if
there is a match then the letter D is returned. If the cell has a different
value #N/A will be returned.

The table is
20.00 D D
30.00 S #N/A
40.00 L #N/A


In order to get the value of the next row the VLOOKUP would need to have the
next rows value in it such as =VLOOKUP(30,A2:B4,2,0).
That is the same as manually entering the letter. They want a formula that
will do this based on the value (20,30,40) entered, automatically. So it
does not matter what value they put in a cell as long as it is an amount
that has a letter associated with it. In the example above, if I change
20.00 to 30.00 the VLOOKUP will return #N/A instead of S.
Click to expand...


I guess I don't understand what you want to do.

Here's what I thought:

You wrote:

"If 20 is entered in cell A1, cell B1 would return the letter D.
However if cell A1 is later changed to 40 then cell B1 would change to the
letter L and so on."

So I assumed your data would be something like:

A1: 20

I gave you a formula to use:

=VLOOKUP(cell_ref,Tbl,2,0)
where cell_ref refers to the cell containing the 20, 40, etc.

So I expected that B1 would contain the formula:

B1: =VLOOKUP(A1,Tbl,2,0)

You also wrote:

"Now imagine a list of jobs, say A1 to A5. Each job might
have a different value."

So I assumed that A1:A5 would contain the numbers 20, 30, 40, or 50 and you
would just fill down the formula you entered in B1.

I also wrote:

===================
Set up a table:

20 D
30 L
40 S
50 P
========================

I don't see this table referenced in your formula.

In YOUR VLOOKUP formula, you have =VLOOKUP(30,A2:B4,2,0)

In place of the cell_ref you have the number 20; and in place of the lookup
table (Tbl), you have entered the range where your data exists.

I don't understand your logic in making these substitutions.

Perhaps it would help if you carefully read HELP for the VLOOKUP worksheet
function. Unless I've completely misunderstood what you are trying to do, I
believe it will, when properly set up, do exactly what you want.
--ron
 
D

DonV

I figured it out, I can't do the testing on the same sheet, need to have the
table on a separate sheet. Now it works.

Thanks Ron for your help and patience.
 
R

Ron Rosenfeld

I figured it out, I can't do the testing on the same sheet, need to have the
table on a separate sheet. Now it works.

Thanks Ron for your help and patience.
Click to expand...

Don,

You certainly CAN have the table on the same sheet. Just put it someplace "out
of the way", where it won't be seen under ordinary circumstances. You can even
"hide" it by collapsing the columns.

But I'm glad you've got it working.
--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with formula 5
Find then highlight in yellow 6
Count the highest number of days without “errors” 2
macro adding rows 8
Excel Formula Dates 5
Too negative XIRR? 3
Autofil on variable rows, delete extract and show remaining rows 3
macro help 1

Top