Hi,
I need to solve a similar problem to this article authored by @Christiaan_Huizer but am having trouble with adding the information to the table. I’m sure I’m missing something basic to do with creating another index so hoping somebody can please point out the error of my ways!?
Our consultants put their time into a roster. It has three statuses:
- Leave - this is their personal time and unbillable
- Travel - all consultants travel to the client sites, this has varying duration for each client/consultant. It is billable but needs to be a distinct line item on the invoice
- On-site - this is billable
What we are trying to do is auto-populate the time sheets each month based on a button click based on the rosters but we only want to pull in the dates that are in the current billable month.
E.g. For the month of August
- Consultant A works a calendar month so Aug 1 - 31th would be in their timesheet.
- Consultant B would be billable Aug 1st - 5th
- Consultant C would be billable Aug 17th - 30th
I have created a lookup that defines the billable dates each month. The button should loop through each row at the start of every month and then populate the table with the consultants name, the date and their status on that day. The consultant then only has to put in the hours they work each for each day.
- We are not concerned with the dates related to leave (this is only used if the consultant blocks out time off for personal matters so they aren’t rostered on).
- We are not concerned with public holidays.
- Some sites work weekends and some consultants work weekends and have weekdays off in lieu so we want to pull all dates in regardless of the day.
