Matching Month specified in a column with the system (computer's) month

[vikrampnz]

Hello

I was struggling to think what should be the subject of this post as I am unsure what this function/process is called. Anyway, my apologies if the subject looks confusing.

Here's what I am trying to achieve:

I have a spreadsheet with a list of people. They are all paid quarterly (three-monthly), however their quarters are different depending upon the date they join. For some people, payments are made Jan-Apr-Jul-Dec for some it is Feb-May-Aug-Jan and so on. I have a column where the payment months are specified in front of each person. I can also make 4 individual columns saying Q1,Q2,Q3,Q4 and specify the months under each if that makes it any easier for Excel to understand.

I run monthly reports on 5th of each month to see who needs to be paid that month. I am trying to figure out a way to automate this process. So Excel will only pick the records that have System's (Computer's) Month mentioned in the Q1,Q2,Q3 or Q4 columns.

Is there a function in excel that can do this or can someone help me with the code for this.

Thanks in advance !

 

[AliGW]

Does this help at all? In G2:

=IF(MATCH(TEXT(TODAY(),"mmm"),$B$2:$E$2,0)>0,"Pay","")

with your staff ID in A2 and the four months in three-letter format in B2 through to E2.

Staff ID	Q1	Q2	Q3	Q4
1001	Jan	Apr	Jul	Oct		Pay	=IF(MATCH(TEXT(TODAY(),"mmm"),$B$2:$E$2,0)>0,"Pay","")

<tbody>
</tbody>

 

[vikrampnz]

Hi....thanks for your prompt response.

I forgot to mention previously, I have around 200 records in this sheet. So what I am looking to run a report (with a command button) that can only select the records that fit the "Month" criteria and paste those in another specified report sheet. I am able to get this part ok, but was struggling with the code for "selecting" the records based on month criteria that need to be pasted in another sheet.

So, in other words, the records that do not match the current month dont need to be included in the report that generated in another worksheet. That way my report will be much shorter than 250 rows.

I would really appreciate your help in this.

Thanks

 

[AliGW]

Yes, fine - so, you set this up as a helper column and then use it as the filter when indexing the records on the second sheet.

In future, please try to give the full picture up front, as it may well change the advice given.

 

[vikrampnz]

or I can create a "Search" box in a designated cell in the same worksheet. I will manually enter the Month in this cell, so that It will only pick the relevant records and paste them in the reports worksheet. This way I can runa report for any month.
Thanks

 

[AliGW]

Yes - fine. I notice that you have posted this same request on another forum: the forum rules in both places ask that you make this clear, so you need to provide links to those cross posts in both places.

 

[vikrampnz]

Could you please guide me as to how this cross-linking of the posts is done? I will get it done immediately.

 

[AliGW]

Yes - go to the other forum and open your thread - copy the address (URL) to the clipboard and then paste it here. Do the same the other way around. Simples!

 

[vikrampnz]

Ok great ! Thank you ...will do it now

 

[vikrampnz]

Matching Computer's Month with the month specified in column