Assign range of pay dates to a specific month in Excel

shine11120 

After the initial three dates you entered*, I placed a formula in column A that calculates the Start Date; the formula can be copied forward as far as needed (or backwards, to all but the first cell).  And, in fact, you may overwrite those calculated dates, if appropriate, with any date from the 24th of the month through the 30th of the month.  In column B I placed a formula that calculates the End Date as one day earlier the Start Date of the next month; overwrite those formulas at your own (minor( peril.

* I stored them as Date values; you may have them as Text values, which is a lousy way to store date information.  (I was able to use a Custom format to display the Start Dates as ordinal dates.)  It's appropriate to store dates as Dates, allowing all the Excel date-related functions and features to be used.  And it's easier and more efficient to convert a Date value to an ordinal Text value than vice versa.

The work this far was easy, but I have to ask about your terminology.  You twice identify "Column 1" and "Column 2"; do you mean Column A and Column B specifically, or are you using R1C1 notation rather than A1 notation (and you are still referring to the first and second columns of the worksheet) ... or do you just mean the "First Column" and "Second Column" relative to something other than the worksheet?  The answer may or may not make a difference to the formulas.

<< I have a table... >>

Is it an Excel table?  I suspect not, as you do not refer to the column names of a table.  I treated the list as an Excel range.  Also, you did not identify whether the transactions are on the same worksheet as the Pay Periods.  I would hope that they are on separate worksheets (otherwise there will be a future problem), but my attached workbook has them combined.

<< * I want to be able to write in a date in Column 1 which automatically populates the corresponding Month in Column 2 according to pay date periods. >>

Easy-peasy, when the Column 1 values and the pay periods are stored as Dates.  But the wider range of possible dates (than the pay period Start Dates and End Dates) means that my Custom format can't handle them.  So I calculate the ordinal equivalent of Column 1 in a separate column, as you can see.

<< * I also want to know if I anyone knows if or how a custom calendar can be created ... >>

Yes, see the Calendar worksheet.  With the current code, it looks up the relevant Pay Period info on the PP&Transactions worksheet using a fixed range (rows 1-13).  How I would fix that to handle more than 13 months depends upon what you choose to store on the PP&Transactions worksheet beneath column A.

I tried to do some conditional formatting on the Calendar worksheet to "highlight" the dates outside the selected Pay Period (such as a gray background), but was not successful.  I have heard that conditional formatting can be tricky.  Background text supposedly can be displayed in Excel cells, but it requires VBA code, and I am not going to attempt that, given how much I am being paid for this.  😉