Using a formula to trigger a formula to calculate

[bearcub]

I'm preparing a schedule of people who are going to be receiving commission advances (draws).

They are eligible to receive the draw the following month of their employee (we pay commissions one months in arrears so if they start in April their first draw would be paid in May).

In one column I have their hire date.

In another column I have a sumif formula to calculate the amount of advances they accumulated year to date (I want the formula to "turn on" when they are supposed to receive this first draw).

How would I tell the Formula to look one month ahead of the hire date to start calculating the draws that are being paid? The though just occurred to me that I could the use the EoMonth function. Would this work?

Michael

 

[DanteAmor]

You could give an example of the data, of which date until which date you want to add and which is the date of reference.

 

[bearcub]

Yes, you're right an example would be good:

Col A15 4/15/2019

Somewhere between B15:S15

I'm going to enter an amount ($5,000) and in the next column I'll going to enter 1 of 3
In the next column I will enter $5,000 followed by 2 of 3
In the 5th column $5,000 and in the 6th column 3 of 3

in T15 I will have a formula that is going to add all the dollars that are paid. However, I would like set this up in advance and not have worry about adding the 5K as each month comes along. I would like the formula to look at the hire date (4/15/2019) and only start calculating when the actaul month is may (I would use the Today or Now function).

I thought that I figure it out but still having issues


thank you for your help

 

[DanteAmor]

Can you complete your example?
You have a date of April 15, what happens in cell T15 when the date is May 1, what happens in T15 when it is jun1 ...
You follow me? you must put the results you expect.

 

[bearcub]

Sorry for the confusion. There will be only 3 subsequent payments made. If the hire date is in april the rep will received 3 payments starting May.

I get the employee information in April and i would like to update the the May-July columns. The only time I don't want the formula to calculated is in April. One of functions in the formula has to be either Today() or Now().

The logic I'm trying to get is that if today is now the following month, May in this example, then the formula calculates.

Some like if Month(Now()) > Month(A15) then run the formula. Otherwise, show nothing.

I hope this explanation helps.

Michael

 

[DanteAmor]

Let's see if I understood.
From April 15 to May 16 is one month, therefore 6,000 are shown.
From April 15 to June 16 are 2 months therefore 9,000 are shown.
From April 15 to June 16 are 3 months, therefore, 14,000 are shown.

In the following example, you can simulate today's date in cell U13.
Tell me if the result in T15 is what you need?

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:18.06px;" /><col style="width:33.27px;" /><col style="width:38.02px;" /><col style="width:33.27px;" /><col style="width:38.02px;" /><col style="width:33.27px;" /><col style="width:38.02px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:20.91px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:125.47px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td style="background-color:#92d050; "> </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Today</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">16-jun</td><td ><--- Date Simulation</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">15-abr</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">6000</td><td >1 of 3</td><td style="text-align:right; ">3000</td><td >2 of 3</td><td style="text-align:right; ">5000</td><td >3 of 3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#ffff00; text-align:right; ">       9,000.00 </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >T15</td><td >{=SUM(OFFSET(A15,0,1,,SMALL(IF(--ISNUMBER(B15:S15),COLUMN(B15:S15)),DATEDIF(A15,U13,"m"))))}</td></tr></table></td></tr></table>


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[DanteAmor]

To show "" in any other case:



{=IFERROR(SUM(OFFSET(A15,0,1,,SMALL(IF(--ISNUMBER(B15:S15),COLUMN(B15:S15)),DATEDIF(A15,U13,"m")))),"")}


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[bearcub]

Thank you. This is actually more than I was thinking but it does give me an idea how I can possibly reduce the monthly maintenance required to track all the draws that need to be send to payroll. I can see that eventually if too much maintenance is required something might be missed - that would be totally embarrassing and not fair to the employee because of my carelessness.

My goal is to update the file with new draws as they come in (update the file with their draw schedule). I would than like to have a new list of draws (on a separate sheet) that are currently due to be paid (sent to payroll for processing).

I know this is different question but do you think it would be possible to create a dynamic list that would update monthly?

For example:

On a separate tab which I would use to send to payroll and the list would expand/contract based upon the number of employees being paid that particular month

In April payroll, based upon your formula, I would have 3 people to be paid

Joey, Herman & Alfred

In May, 4 people (Joey is finished, Jeff and Landry has their first draws)

       April             May
Joey      5,000    Jeff       3,000
Herman    6,000    Herman     2,000
Alfred    3,000    Alfred     1,000
                   Landry       500

Do you think this is possible to do?

Thank you again for your help,

Michael

 

[DanteAmor]

To understand what you need, you must first understand it.
Please, you could perform a complete exercise with real data, with real results, real cell positions, real leaf names.


If you do not put the expected results, I'll have to guess and maybe I'll make a mistake as before.

 

[bearcub]

No you didn't make a mistake before. Your insight actually helped me thing of automating the process further.

I would like to post examples but I can't seem to be able to copy a spreadsheet view, like you did in your example, into the Message board. How do you do that? Is that an addin of some sort that does this? If I had that I could post real examples.

Thank you for your help.

Michael