could you round out the question a little bit? There's a simple answer to the question you asked, but the question itself hints of a deeper issue.
Edit: sorry...I was being cryptic. Could you tell us why you want 12 date fields and what you would use them to do? It seems a little odd to have these 12 fields...perhaps there's a better way to achieve your end goal other than these 12 fields. What is it you are trying to accomplish?
Thanks for your reply. Yes, it is deeper than that but I was trying to go simple.
I have an extensive database that I use as the operations director for a payroll company.
I have to build fields for each client's payroll schedule to create a record for each check date.
The weekly and bi-weeklies are easy (start date +7 or +14). I have semi-monthly check dates that fall on the 1st or 5th or 9th or 15th of the month as well as the 16th, 20th, 22nd, 30th or last day of the month. I know one solution is looping formulas, but I have never been able to figure it out.
Does that help?
"I have to build fields for each client's payroll schedule to create a record for each check date."
If you can possibly make that a record or group of records for each client's schedule, life will be much simpler. That way if you add new clients, you simply create additional records instead of having to define addtional fields.
I assign a code for each client based on tgheir schedule; e.g. a client might be Semi520 which means the checks dates are the 5th and 20th of the month and based on the formulas I have written, they now have a schedule for each month that is the 5th and 20th. The field I am asking about is the first field in a group of related fields that create that schedule.
Here's one way.
YearFld : Number
Date1 : Date ( 5, 1, YearFld )
Date2 : Date ( 5, Month(date1) + 1 , YearFld )
Date3 : Date ( 5, Month(date1) + 2 , YearFld )
and so forth.
Simply enter a year in YearFld, and you get your twelve values.
Thank you very much
I dont think that your solution is properly structured. Perhaps you should find all the clients that have a particular day and then create pay records for them.