Timesheet cut date off help!

**GB_1** · 10-30-2011, 07:59 AM

I've done some trawling through the forum but can't find quite what I'm looking for, maybe someone can help.

I have a calendar sheet which tracks the days I am away on business. The calendar is self dating with each cell in the month having it's own formula to return the correct date.

The cells are colored dependant on the situation, ie holiday, training, away etc.

The days are then totalled by using a COUNTCOLOR function.

My salary, daily bonus away is all taken into account allowing me track my earnings but I can only total up from the first to the last day of the month.

My daily bonus actually works from cut off dates so the totals are not reflecting true monthly earnnings.

I'd like to figure a way of listing the start and end cut off dates per month, ie A1=January start date, B1 = January end date. A2 Feb Start Date, B2 Feb end date etc etc , and then have a macro/formula go through the months and mark a vertical line on the left side of the start cell and right side of the end cell..........and then give me a range from the start to end date that I can refer to in my monthly total cell.

My god this sounds complicated..

Any help would be great

**teylyn** · 10-30-2011, 04:52 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**GB_1** · 10-31-2011, 02:39 AM

OK thanks teylyn. I've attached a dummy copy of the workbook. All data has been changed but I think it's self explanatory......

**teylyn** · 10-31-2011, 05:12 AM

the spreadsheet seems to be missing the VBA for the UDFs.

**GB_1** · 10-31-2011, 05:55 AM

My apologies, hope this is correct....

**teylyn** · 10-31-2011, 06:10 AM

We clearly have different perceptions of "self-explanatory".

I don't even see where bonus is entered, nevermind calculated.

In any case, with a layout like this, any kind of calculations that span across a month end will be impossible to set up with worksheet functions.

You may want to try VBA instead, or re-design your workbook to follow best practices: data entry in a flat table on one sheet, reporting (and your sheet is a report) on a separate sheet. Don't mix the two.

**GB_1** · 10-31-2011, 06:29 AM

I used to have the data entered separately but wanted to bring the input and output into one sheet.

Thanks for your help.

PS.
"We clearly have different perceptions of "self-explanatory"
I suppose we do.