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-
 

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

Count the highest number of days without “errors” 2
Find then highlight in yellow 6
Mid-Month function help 5
Help with formula 5
macro adding rows 8
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Return column values based on criteria (HLOOKUP & VLOOKUP?) 0
Manpower Calendar Distribution, Skipping weekends. 0

Top