Extracting the word "Payments" from file name

[bearcub]

I'm trying to automate my file to extract payments for a certain group. I have to include them in the accrual but have to exclude them from actual payments until the end of the quarter.

I've created a formula that compares the Today's date to a predefined day of the month (the 15th of the month for example).

I do, though, at the end of the quarter (i.e. April, July, Oct & January) need to pay them for the previous 3 months.

I have been excluding payments by manually deleting their payments from the file but I just missed doing this for a couple of people who are going to get paid a month early.

Could I create a trigger that would exclude them for 2 months but include them on the third month?

I just thought that maybe I could use the Today function and compare it to predefined dates in an if statement (5/15, 8/15, 11/15, 2/15)? Commissions are always paid the following month so their quarterly payments would always be paid the month following the quarter.

I was thinking of building this formula in the name manager, would this be possible & how would this look?

If Today(), "mm-dd") => OR(5/15/2022,8/15/2022,11/15/2022,2/15/2023) then Sum(range).

Or would I have to spell this out in the cell formula.

Thank you for your help.

 

[Joe4]

I am thinking you may be able to do what you want with a SUMIFS or SUMPRODUCT forrmula, but the devil is always in the details.

Please provide a small example of what your data looks like and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bearcub]

As I'm writing this I'm realizing that I need to extract the both the month name & type of file (i.e. Accruals, Payments) from the file name.

The Accrual file will always include the amounts.

The complication is in the "Payments" file. I need to show the amounts payable 4 months of the year (May, August, November & January).

I think these are the 3 thresholds I need in the formula. I hope my explanation isn't confusing. Please let me know so I could elaborate further.

1st criteria
Accruals file:

Include the amount payable (I rename the file to Payments after I send the accrual on the 5th business day of the month).

I do have 2 separate files that I send to payroll - one for accruals and another for payments.

The Accrual file will always include the payable amount but the Payments file should only include a payment amount every 3 months.

The file name structures we use are the following:

"Compensation Summary Oct 22 - September Commission Accruals." (due the 5th business day of the month)
OR
"Compensation Summary Oct 22 - September Commission Payments." (sent to payroll at the end of each month)​

2nd criteria
Payments file:

If it isn't at the end of the quarter (May, Aug, Nov, Jan) I need the payment amount to show zero (or blank).
The payment amount should be shown in the accrual file

3rd criteria
Payments File:

If the File name includes one of those 4 months - May, August, November, January -then show the payment amount. Otherwise, hide the amount payable.
The payment amount should be shown in the Accrual file.

For this rep, since this commission is in month 2 of the quarter, $3200 should be in the accruals file but not the payments file.

In November, next month, the $3,200 should appear in both the accruals and payments file.

I presently added the the day condition in the formula that I realize now needs to be replaced with the formula that is dependent upon the month in the file name. The formula works but needs to be modified for those 4 months when the payment needs to be included. (Can that be modified to include the 4 months?).

Thank you for your help in advance

Compensation Summary US Oct 22 - September 22 Commission Payments.xlsb
	F			I						O	P	Q	R
7	Name			September 22						Total Amt Payable	Currency	Earning Type	Comments
97	Michelle Ellis (LEA05CLQZ)			$3,200.28						$0.00	USD	Commission	Paid at quarter end (November payroll)
Comm_Sept 22 US Summary

Cell Formulas
Range	Formula
I97	I97	=SUMIF('Customer Service Quarterly'!$A$3:$A$26,F97,'Customer Service Quarterly'!$B$3:$B$26)
O97	O97	=IF(DAY(TODAY())>=15,0,IF(Q97="Commission",SUM(I97,K97:N97),J97))

 

[bearcub]

I did find this formula to extract Payments from the file name. But each month the text length will change so how do I get around this:

=+MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+13,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-18)

The file name I am using is FY23 YTD US Payments. This formula shows Payments in the cell

FY23 YTD US Payments.xlsb
	K
1	Payments
Pivot Summary

Cell Formulas
Range	Formula
K1	K1	=+MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+13,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-18)

 

[Joe4]

You have seemed to load so much in to your post, I think I "can't see the forest for the trees" (a bit confusing!).
I think it would be best to break this down into individual tasks, and handle one thing at a time.

If you are having trouble extracting something from the file name, show us a representative sample of the different ways that file name may look, and exactly what you are trying to extract out of it.

Also, if the payments happen every three months, shouldn't the months be:
May, August, November, and Februrary
instead of
May, August, November, and Janurary?

If you look at the month numbers, it is clearer to see:
2, 5, 8, and 11 would be every 3 month
1, 5, 8, and 11 seems to follow no pattern

 

[bearcub]

Understood.

I ended up using the following formula. You are correct. It should be February, not January. January is the end of the fiscal year but commissions for January aren't paid until February

The first formula looks at takes the following file names and returns either Accruals or Payments:

Compensation Summary US Oct 22 - September 22 Commission Payments
Compensation Summary US Oct 22 - September 22 Commission Accruals

MID(CELL("filename",'Comm_Sept 22 US Summary'!$A$1),FIND("[",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))+60,FIND("]",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))-FIND("[",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))-65)


This formula looks for the first month in the file name.

=MID(CELL("filename",'Comm_Sept 22 US Summary'!$A$1),FIND("[",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))+25,FIND("]",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))-FIND("[",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))-70)


Is there a way to make these 2 formulas dynamic so I don't have to change the +60, -60 in the first formula or +25, -70 in the second formula to accomodate the change in the file name. Right now I have September - which has 0 characters.

If I were to change the September in the file name to May, June, March or August, for example, the text length would shrink which means i would have to modify the file name search parameters (i.e. 60,65,25,70).

The length of the file string might vary from month to month because of the month names and I would like to avoid having to go into the Name Manager and change those numbers because the total word count in the file name has changed from the previous month.

This it the actual formula I have in the cell to display the results

=IF(QuarterToInclude<>"Nov",0,IF(FileName="Payments",0,IF(Q97="Commission",SUM(I97,K97:N97),J97)))

QuarterToInclude is looking at the second formula and FileName is looking at the first formula. The third IF statement is doing the actual summing based.

This is an example of the output. I did create another file changing Oct to Nov and the 3,200 did populate the cell

Compensation Summary US Oct 22 - September 22 Commission Payments.xlsb
	F			I			L	M	N	O	P	Q	R
6	US Payroll
7	Name			September 22						Total Amt Payable	Currency	Earning Type	Comments
97	Michelle Ellis (LEA05CLQZ)			$3,200.28						$0.00	USD	Commission	Paid at quarter end (November payroll)
Comm_Sept 22 US Summary

Cell Formulas
Range	Formula
I97	I97	=SUMIF('Customer Service Quarterly'!$A$3:$A$26,F97,'Customer Service Quarterly'!$B$3:$B$26)
O97	O97	=IF(QuarterToInclude<>"Nov",0,IF(FileName="Payments",0,IF(Q97="Commission",SUM(I97,K97:N97),J97)))