Calendar and Input Variables Help! EXCEL CHALLENGE!

[dascco]

I need help producing a calendar that highlights certain dates based on multiple inputs or selectable variables. This is for a payroll calendar that is not the same for all clients. I have multiple clients with different pay-cycles and would like to be able to produce their calendar based on selectable input / drop-downs….

For example, Client A's PAY CYCLE (input variable 1) is every 2-weeks (aka bi-weekly/14 days) and their START DATE (input variable 2) with us is on 2/5/2013, for example, so their period would begin 2/5/2013 end 2/18/2013 (next period begin 2/19/2013 end 3/4/2013, etc.). For the 2/5/2013 - 2/18/2013 period their expected PAY DATE for that period is on 2/22/2013 (input variable 3). For that to happen on the backend, we must record their hours either 2 or 3 days prior so CUTOFF PROCESS DATE (input variable 4) for the example would be either 2/20 or 2/19 (i'd select one or the other). Hope someone is still following me

PAY CYCLE = BIWEEKLY, SEMI-MONTHLY (could be 5th and 20th of month or 1st and 15th - not set), or MONTHLY could be 31st, 20th, etc. Varies.

START DATE = could be any day of the month, including weekend or holidays, but would be tied to PAY CYCLE.

PAY DATE (Yeah PAYDAY) = variable AFTER the pay cycle defined and start date plugged in. NOT always going to be set like +3/+4 days after pay period ends. Could be Friday 6 days after period ends or Thursday 2 days after pay period, for example.

CUTOFF PROCESS DATE = Always fixed either 2 days OR 3 days PRIOR PAY DATE. So PAY DATE -2 Days or -3 Days.

Still there?

I want a yearly calendar generated to display (highlight / mark / shade /border) the CUTOFF PROCESS DATE and the PAYDATE.

Here is the catch…. If the PAY DATE falls on a weekend or Holiday, I need to be able to set PAY DATE to either reflect 1ST business day BEFORE or AFTER (input variable 5). Example… PAY DATE falls on Sunday. Most clients want their paycheck friday before. Some want it the next available business day so could be Monday, they elect that on setup. Of course if PAY DATE changes due to wknd/holiday that would mean the CUTOFF PROCESS DATE would be either 2 or 3 days prior that adjusted PAYDATE. Only working with US bank holidays, not customer/client holidays.

Hardest example…. if your tracking… PAY DATE = SAT, NOV 30, 2013. Because Saturday, would need to be adjusted to FRI, NOV 29, 2013 (non Bank Holiday), but because THU, NOV 28, 2013 is a Bank Holiday (Thanksgiving) the CUTOFF PROCESS DATE would have to be either be TUE, NOV 26, 2013 or MON, NOV 25, 2013 based on if I selected 2 or 3 days for CUTOFF PROCESS DATE.

So, what I am trying to accomplish, I cannot simply do a fixed schedule everyone falls into, nor does it seem like a good idea to manually put together a calendar for each client. I'd much rather capture their input with the variables and then have their yearly calendar generated.

Couple other little things, that I don't think would be hard, is some people will always pick a fixed date i.e., EOM so could be 28,29,30,31st depending on the month, leap year, etc.

Could really, really use some help…. Tried WORKDAY function with holidays and more, but only kind of getting there before it gets messy.

[vlady]

Welcome to the forum, maybe a sample workbook with expected sample outcome would help members to solve this one.

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic